tff_dwhzxx.sql

行业应急系统 包括: 应急系统（待遇核发） 发放系统 开发环境:Delphi5 (sp1)+ Oracle 8.05 MultiExport是一个可以将数据按指定字段倒出

-- Start of DDL script for TFF_DWHZXX
-- Generated 25-十月-00  5:17:38 pm
-- from simc-SIDB2000:1

-- Table TFF_DWHZXX

CREATE TABLE tff_dwhzxx
 (
  dwid_ff                    CHAR(10) NOT NULL,
  ffyf                       DATE NOT NULL,
  rs_gh                      NUMBER(10) DEFAULT 0,
  je_gh                      NUMBER(12,2) DEFAULT 0,
  rs_nh                      NUMBER(10) DEFAULT 0,
  je_nh                      NUMBER(12,2) DEFAULT 0,
  rs_zh                      NUMBER(10) DEFAULT 0,
  je_zh                      NUMBER(12,2) DEFAULT 0,
  rs_jh                      NUMBER(10) DEFAULT 0,
  je_jh                      NUMBER(12,2) DEFAULT 0,
  rs_yz                      NUMBER(10) DEFAULT 0,
  je_yz                      NUMBER(12,2) DEFAULT 0,
  rs_hk                      NUMBER(10) DEFAULT 0,
  je_hk                      NUMBER(12,2) DEFAULT 0,
  rs_qt                      NUMBER(10) DEFAULT 0,
  je_qt                      NUMBER(12,2) DEFAULT 0,
  rs_z                       NUMBER(10) DEFAULT 0,
  je_z                       NUMBER(12,2) DEFAULT 0
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
/

-- End of DDL script for TFF_DWHZXX
 
-- Start of DDL script for TFF_DWHZXX
-- Generated 25-十月-00  5:18:40 pm
-- from simc-SIDB2000:1

-- Table TFF_DWHZXX

CREATE TABLE tff_dwhzxx
 (
  dwid_ff                    CHAR(10) NOT NULL,
  ffyf                       DATE NOT NULL,
  rs_gh                      NUMBER(10) DEFAULT 0,
  je_gh                      NUMBER(12,2) DEFAULT 0,
  rs_nh                      NUMBER(10) DEFAULT 0,
  je_nh                      NUMBER(12,2) DEFAULT 0,
  rs_zh                      NUMBER(10) DEFAULT 0,
  je_zh                      NUMBER(12,2) DEFAULT 0,
  rs_jh                      NUMBER(10) DEFAULT 0,
  je_jh                      NUMBER(12,2) DEFAULT 0,
  rs_yz                      NUMBER(10) DEFAULT 0,
  je_yz                      NUMBER(12,2) DEFAULT 0,
  rs_hk                      NUMBER(10) DEFAULT 0,
  je_hk                      NUMBER(12,2) DEFAULT 0,
  rs_qt                      NUMBER(10) DEFAULT 0,
  je_qt                      NUMBER(12,2) DEFAULT 0,
  rs_z                       NUMBER(10) DEFAULT 0,
  je_z                       NUMBER(12,2) DEFAULT 0
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
/

-- End of DDL script for TFF_DWHZXX

-- Start of DDL script for P_FF_DWHZXX
-- Generated 25-十月-00  5:19:35 pm
-- from simc-SIDB2000:1

-- Procedure P_FF_DWHZXX

CREATE OR REPLACE
Procedure          P_FF_DWHZXX                    --单位待遇汇总
( p_DATE      IN DATE,                 --发放日期
  p_ErrorCode   OUT NUMBER             --输出参数：错误代码  若<>0 则运行不正常
)
IS
  v_dwid_ff                    CHAR(10) ;               --单位id
  v_rs_gh                      NUMBER(10) ;         --工行人数
  v_je_gh                      NUMBER(12,2)  ;      --工行金额
  v_rs_nh                      NUMBER(10) ;        --农行人数
  v_je_nh                      NUMBER(12,2) ;      --农行金额
  v_rs_zh                      NUMBER(10) ;        --中行人数
  v_je_zh                      NUMBER(12,2) ;      --中行金额
  v_rs_jh                      NUMBER(10) ;        --建行人数
  v_je_jh                      NUMBER(12,2) ;      --建行金额
  v_rs_yz                      NUMBER(10) ;        --邮政储蓄人数
  v_je_yz                      NUMBER(12,2) ;      --邮政储蓄金额
  v_rs_hk                      NUMBER(10) ;        --汇款人数
  v_je_hk                      NUMBER(12,2) ;      --汇款金额
  v_rs_qt                      NUMBER(10) ;        --其他人数
  v_je_qt                      NUMBER(12,2) ;      --其他金额
  v_rs_z                       NUMBER(10) ;        --总人数
  v_je_z                       NUMBER(12,2) ;       --总金额

  v_dwid                       CHAR(10);        --单位ID
  v_date                       date;            --发放月份

  cursor cur_dwid is
    select dwid_ff from tff_dwxx
  where sjdwdm = '0';
BEGIN
  p_ErrorCode:= 0;

  open cur_dwid;
  loop
    fetch cur_dwid into v_dwid;
    exit when cur_dwid%NOTFOUND;

    --工商银行汇总
    select count(ryid_ff), sum(ffje) into v_rs_gh, v_je_gh
        from tff_ryxx
      where ssdwid = v_dwid and ffjgid = '1000000001';
    if v_je_gh is null then v_rs_gh := null; end if;

    --农业银行汇总
    select count(ryid_ff), sum(ffje) into v_rs_nh, v_je_nh
        from tff_ryxx
      where ssdwid = v_dwid and ffjgid = '1000000002';
    if v_je_nh is null then v_rs_nh := null; end if;

    --中国银行汇总
    select count(ryid_ff), sum(ffje) into v_rs_zh, v_je_zh
        from tff_ryxx
      where ssdwid = v_dwid and ffjgid = '1000000003';
    if v_je_zh is null then v_rs_zh := null; end if;

    --建设银行汇总
    select count(ryid_ff), sum(ffje) into v_rs_jh, v_je_jh
        from tff_ryxx
      where ssdwid = v_dwid and ffjgid = '1000000004';
    if v_je_jh is null then v_rs_jh := null; end if;

    --邮政储蓄汇总
    select count(ryid_ff), sum(ffje) into v_rs_yz, v_je_yz
        from tff_ryxx
      where ssdwid = v_dwid and ffjgid = '1000000006';
    if v_je_yz is null then v_rs_yz := null; end if;

    --邮政汇款汇总
    select count(ryid_ff), sum(ffje) into v_rs_hk, v_je_hk
        from tff_ryxx
      where ssdwid = v_dwid and ffjgid = '1000000007';
    if v_je_hk is null then v_rs_hk := null; end if;

    --其他汇总
    select count(ryid_ff), sum(ffje) into v_rs_qt, v_je_qt
        from tff_ryxx
      where ssdwid = v_dwid and ffjgid not in ('1000000001','1000000002','1000000003',
                                               '1000000004','1000000006','1000000007');
    if v_je_qt is null then v_rs_qt := null; end if;

    --汇总
    select count(ryid_ff), sum(ffje) into v_rs_z, v_je_z
        from tff_ryxx
      where ssdwid = v_dwid;
    if v_je_z is null then v_rs_z := null; end if;

    v_date := to_date((to_char(p_date,'yyyy.mm') || '.01'), 'yyyy.mm.dd');

    delete tff_dwhzxx where dwid_ff = v_dwid and ffyf = v_date;
    insert into tff_dwhzxx(dwid_ff,
                           ffyf,
                           rs_gh,
                           je_gh,
                           rs_nh,
                           je_nh,
                           rs_zh,
                           je_zh,
                           rs_jh,
                           je_jh,
                           rs_yz,
                           je_yz,
                           rs_hk,
                           je_hk,
                           rs_qt,
                           je_qt,
                           rs_z,
                           je_z
                           )
                    values(v_dwid,
                           v_date,
                           v_rs_gh,
                           v_je_gh,
                           v_rs_nh,
                           v_je_nh,
                           v_rs_zh,
                           v_je_zh,
                           v_rs_jh,
                           v_je_jh,
                           v_rs_yz,
                           v_je_yz,
                           v_rs_hk,
                           v_je_hk,
                           v_rs_qt,
                           v_je_qt,
                           v_rs_z,
                           v_je_z
                           );
  end loop;
  close cur_dwid;

  EXCEPTION
    WHEN OTHERS THEN
      p_ErrorCode:= SQLCODE;
END;
/

-- End of DDL script for P_FF_DWHZXX