qcpd.sql

oracle forms developer test

  );
comment on table QP_PDYBBM
  is '判定月报表(多原因)';
comment on column QP_PDYBBM.JHGH
  is '计划钢号';
comment on column QP_PDYBBM.JZH
  is '铸机号';
comment on column QP_PDYBBM.YLSJ
  is '冶炼时间';
comment on column QP_PDYBBM.JHLS
  is '计划炉数';
comment on column QP_PDYBBM.LCLS
  is '炼成炉数';
comment on column QP_PDYBBM.LCL
  is '炼成量';
comment on column QP_PDYBBM.GPL
  is '改判量';
comment on column QP_PDYBBM.GP
  is '改判';
comment on column QP_PDYBBM.HL
  is '回炉';
comment on column QP_PDYBBM.C_G
  is 'C高';
comment on column QP_PDYBBM.C_D
  is 'C低';
comment on column QP_PDYBBM.SI_G
  is 'Si高';
comment on column QP_PDYBBM.SI_D
  is 'Si低';
comment on column QP_PDYBBM.MN_G
  is 'Mn高';
comment on column QP_PDYBBM.MN_D
  is 'Mn低';
comment on column QP_PDYBBM.P_G
  is 'P高';
comment on column QP_PDYBBM.P_D
  is 'P低';
comment on column QP_PDYBBM.S_G
  is 'S高';
comment on column QP_PDYBBM.S_D
  is 'S低';
comment on column QP_PDYBBM.ALT_G
  is 'Alt高';
comment on column QP_PDYBBM.ALT_D
  is 'Alt低';
comment on column QP_PDYBBM.NI_G
  is 'Ni高';
comment on column QP_PDYBBM.NI_D
  is 'Ni低';
comment on column QP_PDYBBM.CR_G
  is 'Cr高';
comment on column QP_PDYBBM.CR_D
  is 'Cr低';
comment on column QP_PDYBBM.CU_G
  is 'Cu高';
comment on column QP_PDYBBM.CU_D
  is 'Cu低';
comment on column QP_PDYBBM.MO_G
  is 'Mo高';
comment on column QP_PDYBBM.MO_D
  is 'Mo低';
comment on column QP_PDYBBM.V_G
  is 'V高';
comment on column QP_PDYBBM.V_D
  is 'V低';
comment on column QP_PDYBBM.RE_G
  is 'Re高';
comment on column QP_PDYBBM.RE_D
  is 'Re低';
comment on column QP_PDYBBM.TI_G
  is 'Ti高';
comment on column QP_PDYBBM.TI_D
  is 'Ti低';
comment on column QP_PDYBBM.NB_G
  is 'Nb高';
comment on column QP_PDYBBM.NB_D
  is 'Nb低';
comment on column QP_PDYBBM.N_G
  is 'N高';
comment on column QP_PDYBBM.N_D
  is 'N低';
comment on column QP_PDYBBM.B_G
  is 'B高';
comment on column QP_PDYBBM.B_D
  is 'B低';
comment on column QP_PDYBBM.CO_G
  is 'Co高';
comment on column QP_PDYBBM.CO_D
  is 'Co低';
comment on column QP_PDYBBM.AS1_G
  is 'As高';
comment on column QP_PDYBBM.AS1_D
  is 'As低';
comment on column QP_PDYBBM.W_G
  is 'W高';
comment on column QP_PDYBBM.W_D
  is 'W低';
comment on column QP_PDYBBM.CEQ4_G
  is 'Ceq4高';
comment on column QP_PDYBBM.CEQ4_D
  is 'Ceq4低';
comment on column QP_PDYBBM.CEQ6_G
  is 'Ceq6高';
comment on column QP_PDYBBM.CEQ6_D
  is 'Ceq6低';
comment on column QP_PDYBBM.QT1_G
  is '其它1高';
comment on column QP_PDYBBM.QT1_D
  is '其它1低';
comment on column QP_PDYBBM.QT2_G
  is '其它2高';
comment on column QP_PDYBBM.QT2_D
  is '其它2低';

prompt
prompt Creating table QP_TFDW
prompt ======================
prompt
create table QP_TFDW
(
  TFDW VARCHAR2(30) not null
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );
comment on table QP_TFDW
  is '退废单位';
comment on column QP_TFDW.TFDW
  is '退废单位';

prompt
prompt Creating table QP_TFSJLR
prompt ========================
prompt
create table QP_TFSJLR
(
  PD_ID  NUMBER,
  TF_ID  NUMBER not null,
  LH     VARCHAR2(10) not null,
  DM     VARCHAR2(15) not null,
  GH_GPQ VARCHAR2(15) not null,
  GH_GPH VARCHAR2(15) not null,
  TFL    NUMBER default 0 not null,
  TFDW   VARCHAR2(60) not null,
  TFSJ   DATE not null,
  ZR_LG  NUMBER default 0 not null,
  ZR_ZG  NUMBER default 0 not null,
  TFYYXH VARCHAR2(6) not null,
  TFYY   VARCHAR2(40) not null,
  XGR    VARCHAR2(16),
  XGSJ   DATE
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );
comment on table QP_TFSJLR
  is '退废处理表';
comment on column QP_TFSJLR.TF_ID
  is 'TF_ID';
comment on column QP_TFSJLR.LH
  is '炉号';
comment on column QP_TFSJLR.DM
  is '断面';
comment on column QP_TFSJLR.GH_GPQ
  is '改判前钢号';
comment on column QP_TFSJLR.GH_GPH
  is '退废判定刚号';
comment on column QP_TFSJLR.TFL
  is '退废量';
comment on column QP_TFSJLR.TFDW
  is '退废单位';
comment on column QP_TFSJLR.TFSJ
  is '退废时间';
comment on column QP_TFSJLR.ZR_LG
  is '炼钢厂责任';
comment on column QP_TFSJLR.ZR_ZG
  is '轧钢厂责任';
comment on column QP_TFSJLR.TFYYXH
  is '退废原因序号';
comment on column QP_TFSJLR.TFYY
  is '退废原因';
comment on column QP_TFSJLR.XGR
  is '修改人';
comment on column QP_TFSJLR.XGSJ
  is '修改时间';
alter table QP_TFSJLR
  add constraint PK_TFSJ_ID primary key (TF_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );

prompt
prompt Creating table QP_TFYY
prompt ======================
prompt
create table QP_TFYY
(
  XH    VARCHAR2(10),
  TK    VARCHAR2(200),
  ZR_ZG NUMBER,
  ZR_LG NUMBER
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );
comment on table QP_TFYY
  is '退废原因';
comment on column QP_TFYY.XH
  is '序号';
comment on column QP_TFYY.TK
  is '条款';
comment on column QP_TFYY.ZR_ZG
  is '轧钢责任';
comment on column QP_TFYY.ZR_LG
  is '炼钢责任';

prompt
prompt Creating table QP_YHGL
prompt ======================
prompt
create table QP_YHGL
(
  YHID VARCHAR2(10),
  YHXM VARCHAR2(10),
  YHKL VARCHAR2(10),
  DWBM VARCHAR2(10),
  DWMC VARCHAR2(40),
  JLRQ VARCHAR2(10),
  GLBZ VARCHAR2(1),
  DLRQ VARCHAR2(30)
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );
comment on table QP_YHGL
  is '用户管理';
comment on column QP_YHGL.YHID
  is '用户ID';
comment on column QP_YHGL.YHXM
  is '用户姓名';
comment on column QP_YHGL.YHKL
  is '用户口令';
comment on column QP_YHGL.DWBM
  is '单位编码';
comment on column QP_YHGL.DWMC
  is '单位名称';
comment on column QP_YHGL.JLRQ
  is '建立日期';
comment on column QP_YHGL.GLBZ
  is '管理员标志';
comment on column QP_YHGL.DLRQ
  is '登录日期';

prompt
prompt Creating table QP_YHQX
prompt ======================
prompt
create table QP_YHQX
(
  YHID  VARCHAR2(10),
  CDHBH VARCHAR2(10),
  CDHMC VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );
comment on table QP_YHQX
  is '用户权限';
comment on column QP_YHQX.YHID
  is '用户ID';
comment on column QP_YHQX.CDHBH
  is '菜单编号';
comment on column QP_YHQX.CDHMC
  is '菜单名称';

prompt
prompt Creating table QP_ZDCP
prompt ======================
prompt
create table QP_ZDCP
(
  GH VARCHAR2(15) not null
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );
comment on table QP_ZDCP
  is '重点产品';
comment on column QP_ZDCP.GH
  is '钢号';

prompt
prompt Creating sequence SEQ_QP_JZSJ_ID
prompt ================================
prompt
create sequence SEQ_QP_JZSJ_ID
minvalue 1
maxvalue 9999999999
start with 8162
increment by 1
nocache
order;

prompt
prompt Creating sequence SEQ_QP_PDSJ_ID
prompt ================================
prompt
create sequence SEQ_QP_PDSJ_ID
minvalue 1
maxvalue 9999999999
start with 6675
increment by 1
nocache
order;

prompt
prompt Creating sequence SEQ_QP_TFSJ_ID
prompt ================================
prompt
create sequence SEQ_QP_TFSJ_ID
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
nocache
order;

prompt
prompt Creating view FRM50_ENABLED_ROLES
prompt =================================
prompt
create or replace view frm50_enabled_roles as
select urp.granted_role role,
sum(distinct decode(rrp.granted_role,
   'ORAFORMS$OSC',2,
   'ORAFORMS$BGM',4,
   'ORAFORMS$DBG',1,0)) flag
from  sys.user_role_privs urp, role_role_privs rrp
where urp.granted_role = rrp.role (+)
  and urp.granted_role not like 'ORAFORMS$%'
group by urp.granted_role
/

prompt
prompt Creating view QP_V_JZSJLR
prompt =========================
prompt
CREATE OR REPLACE VIEW QP_V_JZSJLR AS
SELECT  a.lh, b.jhgh, a.gpgh, a.dm, a.xgr pdr,
          a.shsj ,
          a.kh,
          a.firstk,
          a.lastk,
          DECODE (a.zycf, '2', a.pd_c, c.pd_c) c,
          DECODE (a.zycf, '2', a.pd_si, c.pd_si) si,
          DECODE (a.zycf, '2', a.pd_mn, c.pd_mn) mn,
          DECODE (a.zycf, '2', a.pd_p, c.pd_p) p,
          DECODE (a.zycf, '2', a.pd_s, c.pd_s) s,
          DECODE (a.zycf, '2', a.pd_alt, c.pd_alt) alt,
          DECODE (a.zycf, '2', a.pd_als, c.pd_als) als,
          DECODE (a.zycf, '2', a.pd_ni, c.pd_ni) ni,
          DECODE (a.zycf, '2', a.pd_cr, c.pd_cr) cr,
          DECODE (a.zycf, '2', a.pd_cu, c.pd_cu) cu,
          DECODE (a.zycf, '2', a.pd_mo, c.pd_mo) mo,
          DECODE (a.zycf, '2', a.pd_v, c.pd_v) v,
          DECODE (a.zycf, '2', a.pd_re, c.pd_re) re,
          DECODE (a.zycf, '2', a.pd_ti, c.pd_ti) ti,
          DECODE (a.zycf, '2', a.pd_nb, c.pd_nb) nb,
          DECODE (a.zycf, '2', a.pd_n, c.pd_n) n,
          DECODE (a.zycf, '2', a.pd_b, c.pd_b) b,
          DECODE (a.zycf, '2', a.pd_co, c.pd_co) co,
          DECODE (a.zycf, '2', a.pd_as, c.pd_as) As1,
          DECODE (a.zycf, '2', a.pd_w, c.pd_w) w,
          DECODE (a.zycf, '2', a.pd_ceq4, c.pd_ceq4) ceq4,
          DECODE (a.zycf, '2', a.pd_ceq6, c.pd_ceq6) ceq6,
          DECODE (a.zycf, '2', a.pd_o, c.pd_o) o,
          DECODE (a.zycf, '2', a.pd_s_si, c.pd_s_si) s_si,
          DECODE (a.zycf, '2', a.pd_sb, c.pd_sb) sb,
          DECODE (a.zycf, '2', a.qt1m, c.qt1m) qt1m,
          DECODE (a.zycf, '2', a.qt1, c.qt1) qt1,
          DECODE (a.zycf, '2', a.qt2m, c.qt2m) qt2m,
          DECODE (a.zycf, '2', a.qt2, c.qt2) qt2
     FROM qcpd.qp_jzsjlr a, qcpd.qp_pdsj b, qcpd.qp_pdcfsj c
    WHERE a.pd_id = b.pd_id AND a.pd_id = c.pd_id AND b.shbz = '1'
/

prompt
prompt Creating trigger TRI_QP_GPPDTZ
prompt ==============================
prompt
CREATE OR REPLACE TRIGGER tri_qp_gppdtz
   BEFORE INSERT
   ON qcpd.qp_gppdtz
   FOR EACH ROW
DECLARE
   CURSOR cur_jz
   IS
      SELECT gpgh, dm, cd, zs, zl, bfgpyy
        FROM qcpd.qp_jzsjlr
       WHERE lh = :NEW.lh;

   v_gpgh     qcpd.qp_jzsjlr.gpgh%TYPE;
   v_dm       qcpd.qp_jzsjlr.dm%TYPE;
   v_cd       qcpd.qp_jzsjlr.cd%TYPE;
   v_zs       qcpd.qp_jzsjlr.zs%TYPE;
   v_zl       qcpd.qp_jzsjlr.zl%TYPE;
   v_bfgpyy   qcpd.qp_jzsjlr.bfgpyy%TYPE;
   n          NUMBER                       := 0;
BEGIN
   IF :NEW.lh IS NOT NULL
   THEN
      OPEN cur_jz;

      LOOP
         FETCH cur_jz
          INTO v_gpgh, v_dm, v_cd, v_zs, v_zl, v_bfgpyy;

         EXIT WHEN cur_jz%NOTFOUND;
         n := n + 1;

         IF n = 1
         THEN
            SELECT    v_gpgh
                   || ','
                   || v_dm
                   || '×'
                   || v_cd
                   || '×'
                   || v_zs
                   || ','
                   || TO_CHAR (v_zl)
                   || 'T,'
                   || v_bfgpyy,
                   SUM (zl)
              INTO :NEW.jzsj,
                   :NEW.lcl
              FROM qcpd.qp_jzsjlr
             WHERE lh = :NEW.lh;
         ELSE
            INSERT INTO qcpd.qp_gppdtz_temp
                        (lh1, ylsj1, dm_jz,
                         jzsj
                        )
                 VALUES (:NEW.lh, :NEW.ylsj, v_dm,
                            v_gpgh
                         || ','
                         || v_dm
                         || '×'
                         || v_cd
                         || '×'
                         || v_zs
                         || ','
                         || TO_CHAR (v_zl)
                         || 'T,'
                         || v_bfgpyy
                        );
         END IF;
      END LOOP;

      CLOSE cur_jz;
   END IF;
END tri_qp_gpgptz;
/

prompt
prompt Creating trigger TRI_QP_JZSJ
prompt ============================
prompt
CREATE OR REPLACE TRIGGER tri_qp_jzsj
  BEFORE INSERT ON qcpd.qp_jzsjlr
  FOR EACH ROW
DECLARE
  -- local variables here
BEGIN
  SELECT seq_qp_jzsj_id.NEXTVAL
    INTO :new.jz_id
    FROM DUAL;
END tri_qp_jzsj;
/

prompt
prompt Creating trigger TRI_QP_PDSJ
prompt ============================
prompt
CREATE OR REPLACE TRIGGER tri_qp_pdsj
  BEFORE INSERT ON qcpd.qp_pdsj
  FOR EACH ROW
DECLARE
  -- local variables here
BEGIN
  SELECT seq_qp_pdsj_id.NEXTVAL
    INTO :new.pd_id
    FROM dual;
  INSERT INTO qcpd.qp_pdcfsj
    (pd_id, lh, dm)
  VALUES
    (:new.pd_id, :new.lh, :new.dm_jh);
  DELETE qcpd.qp_pdgcsj;

  INSERT INTO qcpd.qp_pdgcs