otk/otk                                                                      
spool puotk;                                                                  
create table vggod                                                            
(gras     char(6),                                                            
 kceh     CHAR(2),                                                            
 grkl     CHAR(4),                                                            
 namr     CHAR(30),                                                           
 prpv     char(2),                                                            
 vgt      number(14,3),                                                       
 vgg      number(14,3),                                                       
 nmmd     char(22),                                                           
 stdt char(10),stiz char(10),sttn char(4),prn char(1),kgr char(2),prz char(1))
 tablespace zakaz pctfree 5 pctused 75 storage                                
(initial 102400 next 20480 pctincrease 0);                                    
commit;                                                                       
                                                                              
create table cehbrak                                                          
(gras     char(6),                                                            
 kceh     CHAR(2),                                                
 grkl     CHAR(4),                                                
 namr     CHAR(30),                                               
 prpv     char(2),                                                
 kbra     CHAR(2),                                                
 vbrac    number(8,3),                                            
 vbrap    number(8,3),                                            
 vbrai    number(8,3),                                            
 sttn     char(4),                                                
 stdt     char(10),                                               
 stiz     char(10),                                               
 prn      char(1),                                                
 nbrak    char(30),                                               
 nmmd     char(22),                                               
 kgr      char(2),                                                
 prz      char(1),                                                
 npl      char(10)) tablespace zakaz pctfree 5 pctused 75 storage 
(initial 102400 next 20480 pctincrease 0);                        
commit;                                                           
                                                                 
create table dpoter                                              
(gras     char(6),                                               
 kceh     CHAR(2),                                               
 prpv     CHAR(2),                                               
 grkl     CHAR(4),                                               
 namr     CHAR(30),                                              
 nmmd     CHAR(22),                                              
 pbrakc   number(13,2),                                          
 pbrakp   number(13,2),                                          
 pbraki   number(13,2),                                          
 sttn     char(4),                                               
 stdt     char(10),                                              
 stiz     char(10),                                              
 kgr      char(2),                                               
 prz      char(1))                                               
                   tablespace zakaz pctfree 5 pctused 75 storage 
(initial 102400 next 20480 pctincrease 0);                       
commit;                                                          
    
    create table procpc                                              
(gras     char(6),                                               
 kceh     CHAR(2),                                               
 procp    number(6,2),                                           
 procpi   number(6,2),                                           
 sttn     char(4),                                               
 stdt     char(10),                                              
 stiz     char(10),                                              
 prsum    number(6,2),                                           
 prsumi   number(6,2),                                           
 kgr      char(2),                                               
 prz      char(1))                                               
                   tablespace zakaz pctfree 5 pctused 75 storage 
(initial 102400 next 20480 pctincrease 0);                       
commit;                                                          
.rem    create table tnary                                                 
.rem    (gras     char(6),                                                 
.rem    kceh     CHAR(2),                                                  
.rem    kgr      char(2),                                                  
.rem    knary    char(7),                                                  
.rem    nnary    CHAR(200),                                                
.rem    stdt char(10),                                                     
.rem    stiz char(10),                                                     
.rem    kol      number(5))                                                
.rem                      tablespace zakaz pctfree 5 pctused 75 storage    
.rem   (initial 102400 next 20480 pctincrease 0);                          
.rem   commit;                                                             
                                                                           
create table prihin                                                        
(gras     char(6),                                                         
 kceh     CHAR(2),                                                         
 kprih    char(2),                                                         
 nprih    CHAR(80),                                                        
 vprih    number(6,1),                                                     
 stdt char(10),                                                            
 stiz char(10),                                                                                                                             
      kgr      char(2))                                                       
                   tablespace zakaz pctfree 5 pctused 75 storage         
(initial 102400 next 20480 pctincrease 0);                               
commit;                                                                  
                                                                         
                                                                         
create table tplav                                                       
(gras     char(6),                                                       
 kceh     CHAR(2),                                                       
 kol      number(5),                                                     
 stdt char(10),                                                          
 stiz char(10),                                                          
 kgr     char(2))                                                        
                   tablespace zakaz pctfree 5 pctused 75 storage         
(initial 102400 next 20480 pctincrease 0);                               
commit;                                                                  
                                                                         
create table rubeg                                                       
(kceh     CHAR(2),                                                       
 procr    number(6,2),                                                   
 gras     char(6),                                                       
  stdt char(10),                                                     
 stiz char(10),                                                     
 kgr      char(2))                                                  
                   tablespace zakaz pctfree 5 pctused 75 storage    
(initial 102400 next 20480 pctincrease 0);                          
commit;                                                             
                                                                    
                                                                    
create table tpred                                                  
(gras     char(6),                                                  
 kceh     CHAR(2),                                                  
 kol      number(5),                                                
 stdt char(10),                                                     
 stiz char(10),                                                     
 kgr     char(2))                                                   
                   tablespace zakaz pctfree 5 pctused 75 storage    
(initial 102400 next 20480 pctincrease 0);                          
commit;                                                             
                                                                    
    exit;                     

otk/otk
spool bazac                      
drop table bazac;                
commit;                          
create table bazac               
(gras     char(6),               
 kceh     CHAR(2),               
 prpv     CHAR(2),               
 vbrai    number(11,3),          
 procpg   number(7,3),           
 pbraki   number(16,3),          
 prsum    number(7,3),           
 vbrai1   number(8,3),           
 vbrai2   number(8,3),           
 vbrai3   number(8,3),           
 vbrai4   number(8,3),           
 vbrai5   number(8,3),           
 vbrai6   number(8,3),           
 vbrai7   number(8,3),           
 vbrai8   number(8,3),           
 vbrai9   number(8,3),           
 vbrai10  number(8,3),           
  vbrai11  number(8,3),       
 vbrai12  number(8,3),       
 vbrai13  number(8,3),       
 vbrai14  number(8,3),       
 vbrai15  number(8,3),       
 vbrai16  number(8,3),       
 vbrai17  number(8,3),       
 vbrai18  number(8,3),       
 vbrai19  number(8,3),       
 vbrai20  number(8,3),       
 vbrai21  number(8,3),       
 vbrai22  number(8,3),       
 vbrai23  number(8,3),       
 vbrai24  number(8,3),       
 vgt      number(14,3),      
 kgr      char(2),           
 procr    number(7,2),       
 proci    number(6,2),       
 kolp     number(5),         
 koln     number(5),         
 kolpz    number(5),         
  kolpd    number(5),                                                          
 vdef     number(12,3),                                                       
 prz      char(1),procpgp number(5,2),prsump number(5,2)) tablespace zakaz pctfree 5 pctused 75 storage
(initial 102400 next 20480 pctincrease 0);                                    
commit;                                                                       
                                                                              
drop space ptab;                                                              
                                                                              
commit;                                                                       
                                                                                                                      
accept dat char prompt 'Введите год для формирования базы предыдущего года(гггг):'                                                                             
                                                                              
insert into bazac (kceh,prpv,vbrai) select kceh,prpv,sum(vbrai/1000)
        from cehbrak where grkl is not null and substr(gras,3,4)='&dat'      
  group by kceh,prpv;                                                        
commit;                                                                                                                                                                                                                             
                                                                            
insert into bazac (kceh,vbrai,vbrai1,                                        
                                vbrai2,                                      
                                vbrai3,                                      
                                vbrai4,                                      
                                vbrai5,                                      
                               vbrai6,                                      
                                vbrai7,                                      
                               vbrai8,                                      
                               vbrai9,                                      
                               vbrai10,                                     
                               vbrai11,                                     
                               vbrai12,  
                                vbrai13,                       
                                vbrai14,                       
                                vbrai15,                       
                                vbrai16,                       
                                vbrai17,                       
                                vbrai18,                       
                                vbrai19,                       
                                vbrai20,                       
                                vbrai21,                       
                                vbrai22,                       
                                vbrai23,                       
                                vbrai24)                       
                                 select kceh,sum(vbrai),       
                            sum(vbra1),                        
                            sum(vbra2),                        
                            sum(vbra3),                        
                            sum(vbra4),                        
                            sum(vbra5),                        
                            sum(vbra6),                        
                            sum(vbra7),                        
                            sum(vbra8),
                                                          sum(vbra9),                                     
                            sum(vbra10),                                    
                            sum(vbra11),                                    
                            sum(vbra12),                                    
                            sum(vbra13),                                    
                            sum(vbra14),                                    
                            sum(vbra15),                                    
                            sum(vbra16),                                    
                            sum(vbra17),                                    
                            sum(vbra18),                                    
                            sum(vbra19),                                    
                            sum(vbra20),                                    
                            sum(vbra21),                                    
                            sum(vbra22),                                    
                            sum(vbra23),                                    
                            sum(vbra24)                                     
             from markgp                                                    
  group by kceh;                                                            
commit;                                                                     

 insert into bazac (kceh,vbrai,vbrai1,              
                                vbrai2,            
                                vbrai3,            
                                vbrai4,            
                                vbrai5,            
                                vbrai6,            
                                vbrai7,            
                                vbrai8,            
                                vbrai9,            
                                vbrai10,           
                                vbrai11,           
                                vbrai12,           
                                vbrai13,           
                                vbrai14,           
                                vbrai15,           
                                vbrai16,           
                                vbrai17,           
                                vbrai18,           
                                vbrai19,           
                                vbrai20,           
                                vbrai21,           
                                          vbrai22,                         
          vbrai23,                         
          vbrai24)                         
           select kceh,sum(vbrai),         
      sum(vbra1),                          
      sum(vbra2),                          
      sum(vbra3),                          
      sum(vbra4),                          
      sum(vbra5),                          
      sum(vbra6),                          
      sum(vbra7),                          
      sum(vbra8),                          
      sum(vbra9),                          
      sum(vbra10),                         
      sum(vbra11),                         
      sum(vbra12),                         
      sum(vbra13),                         
      sum(vbra14),                         
      sum(vbra15),                         
      sum(vbra16),                         
      sum(vbra17),                         
      sum(vbra18),                         
                            sum(vbra19),                                    
                            sum(vbra20),                                    
                            sum(vbra21),                                    
                            sum(vbra22),                                    
                            sum(vbra23),                                    
                            sum(vbra24)                                     
             from markgm                                                    
  group by kceh;                                                            
commit;                                                                                                                                                             
                                                                            
update bazac set pbraki =                                                   
      (select sum(pbrakc / 1000) from dpoter where substr(gras,3,4)='&dat'  
and bazac.kceh=dpoter.kceh and bazac.prpv=dpoter.prpv group by kceh,prpv)   
  where  (kceh||prpv) in (select kceh||prpv from dpoter);                   
commit;                                                                                   
                                                                            
update bazac set pbraki =                                                   
            (select sum(pbrakc / 1000) from dpoter where substr(gras,3,4)='&dat'
            and bazac.kceh=dpoter.kceh                                      
            group by kceh)                                                  
  where prpv is null and (kceh) in (select kceh from dpoter);               
commit;                                                                     
            
update bazac set vgt =                                                      
            (select sum(vgt)/1000 from vggod where substr(gras,3,4)='&dat'  
         and vggod.prn = '*'  and prpv is not null                          
and vggod.kceh=bazac.kceh and bazac.prpv=vggod.prpv                         
    group by kceh,prpv)                                                     
  where prpv is not null and (kceh||prpv) in (select kceh||prpv from vggod  
      where prpv is not null and prn ='*' and substr(gras,3,4)='&dat');     
commit;                                                                                                                                             
                                                                
update bazac set vgt =                                                       
            (select sum(vgt)/1000 from vggod where substr(gras,3,4)='&dat'   
         and vggod.prn = '*'  and prpv is not null                           
and vggod.kceh=bazac.kceh and bazac.prpv=vggod.prpv                          
    group by kceh,prpv)                                                      
  where prpv is not null and (kceh||prpv) in (select kceh||prpv from vggod   
      where prpv is not null and prn ='*' and substr(gras,3,4)='&dat');      
commit;                                                                      
                                                                    
update bazac set vgt =                                                       
(select sum(vgt)/1000 from vggod where substr(gras,3,4)='&dat' and vggod.prn 
is null and vggod.prpv ='*' and vggod.kceh=bazac.kceh                        
    group by kceh)                                                           
  where prpv is null and (kceh) in (select kceh from vggod where prpv='*');  
commit;                                                                      
                                                                                                 
update bazac set koln = (select sum(kol) from tnary where substr(gras,3,4)='&dat'
and tnary.kceh=bazac.kceh                                                  
    group by kceh)                                                         
where prpv is null and (kceh) in (select kceh from tnary);                 
commit;                                                                                  
                                                                           
update bazac set kolp =                                                    
(select sum(kol) from tplav where substr(gras,3,4)='&dat' and              
                               tplav.kceh=bazac.kceh                       
    group by kceh)                                                         
  where prpv is null and (kceh) in (select kceh from tplav);
     commit;                                                               
                                                      
update bazac set kolpz=                                               
(select sum(kol) from tpred where substr(gras,3,4)='&dat' and         
                               tpred.kceh=bazac.kceh                  
    group by kceh)                                                    
  where prpv is null and (kceh) in (select kceh from tpred);          
commit;                                                               
                                                                    
update bazac set procpg=                                                     
(select prsum from procpc where gras='12'||'&dat' and                        
                               bazac.kceh=kceh)                              
  where  prpv is null and kceh in (select kceh from procpc where gras='12'||'&dat');
commit;                                                                      
                                                                             
insert into bazac (gras,kceh,procpg,kgr,prz) select gras,kceh,prsum,kgr,prz  
  from procpc    where gras='12'||'&dat' and kceh='69';                      
                                                                             
insert into bazac (gras,kceh,procpg,kgr,prz) select gras,kceh,prsum,kgr,prz  
  from procpc    where gras='12'||'&dat' and kceh='70';                      
                                                                             
update bazac (vgt,koln,kolp,kolpz)=                                          
              select sum(vgt),sum(koln),sum(kolp),sum(kolpz)                 
 from bazac                                                                  
 where kceh in ('06','18') and prpv is null)                                 
where kceh='18' and prpv is null;                                            
                                                                                                                                                          
update bazac set procr = (nvl(vbrai,0)/nvl(vgt,0))*100 where prpv is null and                                     
(vgt is not null and  nvl(vgt,0)!=0)
            and (vbrai is not null and  nvl(vbrai,0)!=0);); 
commit;                                 
 delete from bazac                                           
where kceh='06' and prpv is null;                           
                                                            
update bazac set gras='12'||&dat;                           
commit;                                                     
                                                            
update bazac                                                
set prpv='44'                                               
where kceh='06' and prpv='00';                              
                                                            
update bazac                                                
set prpv='36'                                               
where kceh='18' and prpv='00';                              
                                                            
commit;                                                     
               
update bazac set                                            
  prz = 'o' where kceh in ('10','24');                      
                                                                           
update bazac set                                                         
  prz = 's' where kceh in ('01','02','03','12','16','21','61','25');     
commit;                                                                  
                                                                         
update bazac set                                                         
  prz = 'p' where kceh in ('04','05','06','07','09','18','19','20','28', 
                                                         '40');          
commit;                                                                                                                                          
                                                                         
update bazac set                                                         
  kgr = null where kceh='62';                                            
commit;                                                                  
update bazac set                                                         
  kgr = '0' where kceh  in ('01','02','03','12','16','61','21','25');    
commit;                                                                  
update bazac set                                                         
  kgr='9' where kceh in ('10');                                          
  commit;                                                                
update bazac set                                                         
  kgr = '1' where kceh ='04'                                             
    and kgr is null;                        
commit;                                    
update bazac set                           
  kgr = '2' where kceh in ('07')           
   and kgr is null;                        
commit;                                    
                                                                                      
update bazac set                           
  kgr = '3' where kceh ='05'               
   and kgr is null;                        
commit;                                    
update bazac set                           
  kgr = '4' where kceh  in ('19','40')     
   and kgr is null;                        
commit;                                    
update bazac set                           
  kgr = '5' where kceh = '28'              
   and kgr is null;                        
commit;                                    
update bazac set                           
  kgr = '6' where kceh='06'                
        and kgr is null;                                          
commit;                                                      
update bazac set                                             
  kgr = '7' where kceh='09'                                  
   and kgr is null;                                          
commit;                                                      
update bazac set                                             
  kgr ='8' where kceh ='18'                                  
   and kgr is null;                                          
commit;                                                      
                                                             
update bazac set                                             
  kgr = '8' where kceh ='20'                                 
   and kgr is null;                                          
commit;                                                        
                                                             
insert into bazac(gras,kceh)                                 
values ('12'||'&dat','91');                                  
                                                                               
insert into bazac(gras,kceh)                                                 
values ('12'||'&dat','92');                                                  
                                                                             
insert into bazac(gras,kceh)                                                 
values ('12'||'&dat','99');                                                  
                                                                             
commit;                                                                      
                                                                             
update bazac set                                                             
(pbraki,vgt,vbrai,vbrai1,vbrai2,vbrai3,vbrai4,vbrai5,vbrai6,vbrai7,vbrai8,vbrai9,
vbrai10,vbrai11,vbrai12,vbrai13,vbrai14,vbrai15,vbrai16,vbrai17,vbrai18,     
vbrai19,vbrai20,vbrai21,vbrai22,vbrai23,vbrai24)                             
= (select                                                                    
sum(pbraki),sum(vgt),sum(vbrai),sum(vbrai1),sum(vbrai2),sum(vbrai3),sum(vbrai4),
sum(vbrai5),sum(vbrai6),sum(vbrai7),sum(vbrai8),sum(vbrai9),sum(vbrai10),    
sum(vbrai11),sum(vbrai12),sum(vbrai13),sum(vbrai14),sum(vbrai15),sum(vbrai16),
sum(vbrai17),sum(vbrai18),sum(vbrai19),sum(vbrai20),sum(vbrai21),sum(vbrai22),
sum(vbrai23),sum(vbrai24)                                                    
    from bazac                                                               
       where prpv is null and prz='s'                                        
       group by prz)                                                                                                                    
         where kceh='91';                                                              
                                                                              
commit;                                                                       
                                                                              
update bazac set                                                              
(pbraki,vgt,vbrai,vbrai1,vbrai2,vbrai3,vbrai4,vbrai5,vbrai6,vbrai7,vbrai8,vbrai9,
vbrai10,vbrai11,vbrai12,vbrai13,vbrai14,vbrai15,vbrai16,vbrai17,vbrai18,      
vbrai19,vbrai20,vbrai21,vbrai22,vbrai23,vbrai24)                              
= (select                                                                     
sum(pbraki),sum(vgt),sum(vbrai),sum(vbrai1),sum(vbrai2),sum(vbrai3),sum(vbrai4),
sum(vbrai5),sum(vbrai6),sum(vbrai7),sum(vbrai8),sum(vbrai9),sum(vbrai10),     
sum(vbrai11),sum(vbrai12),sum(vbrai13),sum(vbrai14),sum(vbrai15),sum(vbrai16),
sum(vbrai17),sum(vbrai18),sum(vbrai19),sum(vbrai20),sum(vbrai21),sum(vbrai22),
sum(vbrai23),sum(vbrai24)                                                     
     from bazac                                                               
       where prpv is null and prz='p'                                         
       group by prz)                                                          
where kceh='92';                                                              
                                                                              
commit;
                                                                             
update bazac set                                                             
(pbraki,vgt,vbrai,vbrai1,vbrai2,vbrai3,vbrai4,vbrai5,vbrai6,vbrai7,vbrai8,vbrai9,
vbrai10,vbrai11,vbrai12,vbrai13,vbrai14,vbrai15,vbrai16,vbrai17,vbrai18,     
vbrai19,vbrai20,vbrai21,vbrai22,vbrai23,vbrai24)                             
= (select                                                                    
sum(pbraki),sum(vgt),sum(vbrai),sum(vbrai1),sum(vbrai2),sum(vbrai3),sum(vbrai4),
sum(vbrai5),sum(vbrai6),sum(vbrai7),sum(vbrai8),sum(vbrai9),sum(vbrai10),    
sum(vbrai11),sum(vbrai12),sum(vbrai13),sum(vbrai14),sum(vbrai15),sum(vbrai16)
sum(vbrai17),sum(vbrai18),sum(vbrai19),sum(vbrai20),sum(vbrai21),sum(vbrai22)
sum(vbrai23),sum(vbrai24)                                                    
                from bazac                                                   
       where prpv is null and prz in ('c','s','p','o'))                      
where kceh='99';                                                             
                                                                             
update bazac                                                                 
set prsum=(select distinct prsumi from procpc                                
           where gras='12'||'&dat')                                          
where kceh='99';                                                             
                                                                             
commit;                                                                      
exit;                                                                        
 create index BCGRAS98 on BAZAC(GRAS);                                        
create index BCKCEH98 on BAZAC(KCEH);                                        
create index BCPRPV98 on BAZAC(PRPV);                                      
                                                                             
exit;                                                                        
                                                                             
  разнести дополн, если нет в cehbrak                                  
                                                                             
insert into bazac (kceh,pbraki) select kceh,sum(pbrakc/1000)                 
from dpoterc where substr(gras,3,2)='98' and kceh='05' group by kceh;        
commit;                                                                      
                                                                             
update bazac set prz='p',prz='3', gras='1298' where kceh='05' and prpv is      
null;
                                                                             
exit;