rendered paste body
select t.codgr, t.datetr, t.codbr, t.codplant, t.codtr, t.flagcredit,
t.summaincur, t.codinref2, t.codinref3, t.codinref2501,
t.acc1, t.subacc1, t.acc2, t.subacc2
from acctransreportview t, accregistrotklamort_prm prm
where t.datetr between prm.dbegin and prm.dend
and t.codbr = prm.ncodbr -- код филиала
and t.flagofficial = 1
and
(
( t.acc1 = 90 and t.subacc1 = 2
and (t.acc2 = 41 or t.acc2 = 44)
)
or
( t.acc1 = 90 and t.subacc1 = 1
and (t.acc2 = 62 or t.acc2 = 50)
)
or
( t.acc1 = 90 and t.subacc1 = 3
and t.acc2 = 68
)
)
select * from accregistrotklamort_prm t
1 01.06.2006 30.06.2006 4201 4
-- Create table
create table ACCREGISTROTKLAMORT_PRM
(
DBEGIN DATE,
DEND DATE,
NCODBR INTEGER,
NCODPLAN INTEGER,
NCODPLANT INTEGER,
NEXPARAM INTEGER,
NEXPARAM1 INTEGER,
NEXPARAM2 INTEGER,
NEXPARAM3 INTEGER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create table
create table ACCTRANSREPORTVIEW
(
CODTR INTEGER not null,
DATETR DATE,
CODBR INTEGER,
ACC1 INTEGER,
SUBACC1 INTEGER,
ACC2 INTEGER,
SUBACC2 INTEGER,
FLAGCREDIT INTEGER not null,
PERIOD INTEGER,
CODCUR INTEGER,
SUMMAINCUR FLOAT,
SUMCALCCUR FLOAT,
SUMSUBACCCUR FLOAT,
QUANT FLOAT,
FLAGTRANSED INTEGER,
DESCR VARCHAR2(254),
CODGR INTEGER,
SYSOWNER VARCHAR2(20),
CREATOR VARCHAR2(8),
CREATEDATE INTEGER,
EDITOR VARCHAR2(20),
LASTUPDATE INTEGER,
CODINREF2 INTEGER,
CODGR2 INTEGER,
CODSUBGR2 INTEGER,
CODINREF3 INTEGER,
CODGR3 INTEGER,
CODSUBGR3 INTEGER,
CODINREF4 INTEGER,
CODGR4 INTEGER,
CODSUBGR4 INTEGER,
CODINREF5 INTEGER,
CODGR5 INTEGER,
CODSUBGR5 INTEGER,
CODPLAN INTEGER not null,
CODPLANT INTEGER not null,
FLAGREAL INTEGER,
FLAGOFFICIAL INTEGER,
CODACC1 INTEGER,
CODACC2 INTEGER,
CODINREF501 NUMBER,
CODGR501 NUMBER,
CODSUBGR501 NUMBER,
CODINREF601 NUMBER,
CODGR601 NUMBER,
CODSUBGR601 NUMBER,
CODINREF701 NUMBER,
CODGR701 NUMBER,
CODSUBGR701 NUMBER,
CODINREF2201 INTEGER,
CODGR2201 INTEGER,
CODSUBGR2201 INTEGER,
CODINREF2301 INTEGER,
CODGR2301 INTEGER,
CODSUBGR2301 INTEGER,
CODINREF2401 INTEGER,
CODGR2401 INTEGER,
CODSUBGR2401 INTEGER,
CODINREF2501 INTEGER,
CODGR2501 INTEGER,
CODSUBGR2501 INTEGER,
CODINREF2601 INTEGER,
CODGR2601 INTEGER,
CODSUBGR2601 INTEGER,
CODINREF2701 INTEGER,
CODGR2701 INTEGER,
CODSUBGR2701 INTEGER,
CODINREF4201 INTEGER,
CODGR4201 INTEGER,
CODSUBGR4201 INTEGER,
CODINREF6201 INTEGER,
CODGR6201 INTEGER,
CODSUBGR6201 INTEGER,
CODINREF8201 INTEGER,
CODGR8201 INTEGER,
CODSUBGR8201 INTEGER,
CODINREF8301 INTEGER,
CODGR8301 INTEGER,
CODSUBGR8301 INTEGER,
CODINREF8401 INTEGER,
CODGR8401 INTEGER,
CODSUBGR8401 INTEGER,
CODINREF10201 INTEGER,
CODGR10201 INTEGER,
CODSUBGR10201 INTEGER,
CODINREF12201 INTEGER,
CODGR12201 INTEGER,
CODSUBGR12201 INTEGER,
CODINREF14201 INTEGER,
CODGR14201 INTEGER,
CODSUBGR14201 INTEGER,
CODINREF16201 NUMBER,
CODGR16201 NUMBER,
CODSUBGR16201 NUMBER,
CODINREF18301 INTEGER,
CODGR18301 INTEGER,
CODSUBGR18301 INTEGER,
CODINREF18401 INTEGER,
CODGR18401 INTEGER,
CODSUBGR18401 INTEGER,
CODINREF18201 NUMBER,
CODGR18201 NUMBER,
CODSUBGR18201 NUMBER,
CODINREF20201 INTEGER,
CODGR20201 INTEGER,
CODSUBGR20201 INTEGER,
CODINREF22201 INTEGER,
CODGR22201 INTEGER,
CODSUBGR22201 INTEGER,
CODINREF24201 INTEGER,
CODGR24201 INTEGER,
CODSUBGR24201 INTEGER,
CODINREF26201 INTEGER,
CODGR26201 INTEGER,
CODSUBGR26201 INTEGER,
CODINREF28201 INTEGER,
CODGR28201 INTEGER,
CODSUBGR28201 INTEGER,
CODINREF30201 INTEGER,
CODGR30201 INTEGER,
CODSUBGR30201 INTEGER,
CODINREF32201 INTEGER,
CODGR32201 INTEGER,
CODSUBGR32201 INTEGER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1222M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ACCTRANSREPORTVIEW
add constraint PK_ACCTRANSREPORTVIEW primary key (CODTR, FLAGCREDIT);
alter table ACCTRANSREPORTVIEW
add foreign key (CODACC1)
references ACC (CODACC);
alter table ACCTRANSREPORTVIEW
add foreign key (CODACC2)
references ACC (CODACC);
-- Create/Recreate indexes
create unique index ACCTRANSIDXPK on ACCTRANSREPORTVIEW (CODTR, FLAGCREDIT)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 108M
minextents 1
maxextents unlimited
);
create index IDXACCTRANSACCPLANDATE on ACCTRANSREPORTVIEW (DATETR, CODPLANT, ACC1, SUBACC1)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 162M
minextents 1
maxextents unlimited
);
create index IDXACCTRANSRVDATE on ACCTRANSREPORTVIEW (DATETR)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 108M
minextents 1
maxextents unlimited
);