CREATE OR REPLACE PROCEDURE SP_ZY_XMFYCX
(
     as_KSRQ00    IN   CHAR,
     as_JSRQ00    IN   CHAR,
     as_KDKS00    IN   CHAR,
     AS_JSFS00    in   CHAR default '-1', --  -1: 全部  0：未结算 1：已结算
     as_FYTJ00    in   CHAR default '-1', --  -1: 全部  0：费用  1：材料
     ad_ID0000    OUT  NUMBER,
     as_YHMSG0    OUT  VARCHAR,
     as_SYSMSG    OUT  VARCHAR
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhangyc     2011.11.09  create;
as
BEGIN
    Select  SQ_BM_YYSFTJ_ID0000.nextval  into  ad_ID0000  from  dual;


   ---GHRQ00:项目名称 BEIZHU：核算项目名称  BEIZH1:项目规格 BEIZH2：项目单位 GHKS100：项目单价 ，GHKS101：项目数量  GHKS102：项目金额
   insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS99,BEIZHU,BEIZH1,BEIZH2,GHKS100,GHKS101,GHKS102,DYID00)
    select
      ad_ID0000,
      Trim(A.XMMC00)XMMC00,  --项目名称
      D.HSXMID,
      D.HSXMMC,--核算项目名,
      trim(substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50)) as  XMGG00,    --项目规格
      trim(C.DW0000) DW0000 ,      --单位
      round(sum(A.HJJE00)/decode(sum(A.XMSL00),0,1,sum(A.XMSL00)),2) XMDJ00  ,      --单价
      sum(A.XMSL00)XMSL00 ,  --数量
      sum(A.HJJE00)HJJE00 ,
      0
from  ZY_FYMX00 A,ZY_BRFY00 B,BM_YYSFXM C,VW_BM_YJHSXM D
where A.DJH000 = B.DJH000
  and A.XMBH00 = C.SFXMID
  and  C.HSXMID = d.HSXMID
  and A.XMBH00 < 9999999990
  and  B.czrq00>=as_KSRQ00
  and  B.czrq00<=as_JSRQ00
  and  (a.zxks00 =as_KDKS00 or as_KDKS00='-1')
  and  ((AS_JSFS00='0' and b.jzdh00+0=0) or (AS_JSFS00='1' and b.jzdh00>0)  or (AS_JSFS00='-1' and 1=1))
  group by ad_ID0000,A.XMMC00,D.HSXMID,D.HSXMMC,
  trim(substrb(decode(C.JSXM00,'2',C.GG0000,C.BZ0000),1,50)),C.DW0000;

 insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS99,BEIZHU,BEIZH1,BEIZH2,GHKS100,GHKS101,GHKS102,DYID00)
 select
      ad_ID0000,
      Trim(C.YPMC00),
      F.HSXMID,
      F.HSXMMC ,
      E.CWNR00,
      C.CFDW00  ,
      C.LSDJ00  ,
      (DECODE(C.SFZBY0,'Y',0,C.YPZSL0)),
      (round(C.LSDJ00*DECODE(C.SFZBY0,'Y',0,C.YPZSL0)*100)/100)HJJE00,
      0
from  ZY_BRFY00 A,yf_ZYCF00 B,yf_zycfmx C,BM_YPLB00 D,BM_YYSFXM E,VW_BM_YJHSXM F
where  A.DJH000 = B.DJH000
  and  B.cflsh0 = C.cflsh0
  and  B.YPDLBH = D.LBBH00
  and  D.SFDM00 = E.SFXMID
  and  e.HSXMID = f.HSXMID
  and  a.czrq00>=as_KSRQ00
  and  a.czrq00<=as_JSRQ00
  and   b.djh000 in  (select djh000 from zy_fymx00 where  (zxks00 =as_KDKS00 or as_KDKS00='-1')and  czrq00>=as_KSRQ00 and czrq00<=as_JSRQ00 and XMBH00 > 9999999990)
  and  ((AS_JSFS00='0' and a.jzdh00+0=0) or (AS_JSFS00='1' and a.jzdh00>0) or (AS_JSFS00='-1' and 1=1) );


 insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS99,BEIZHU,BEIZH1,BEIZH2,GHKS100,GHKS101,GHKS102,DYID00)
select
      ad_ID0000,
      Trim(B.YPMC00)YPMC00,
      F.HSXMID,
      F.HSXMMC ,
      E.CWNR00,
      B.QLDW00,
      B.LSDJ00,
      B.YPZSL0,
      ROUND(B.LSDJ00*B.YPZSL0,2),
      0
from  ZY_BRFY00 A,yf_yzypsq B,BM_YPLB00 D,BM_YYSFXM E,VW_BM_YJHSXM F
where  A.DJH000 = B.DJH000
  and  B.YPDLBH = D.LBBH00
  and  e.HSXMID = f.HSXMID
  and  D.SFDM00 = E.SFXMID
  and  a.czrq00>=as_KSRQ00
  and  a.czrq00<=as_JSRQ00
  and   b.djh000 in  (select djh000 from zy_fymx00 where  (zxks00 =as_KDKS00 or as_KDKS00='-1')and  czrq00>=as_KSRQ00 and czrq00<=as_JSRQ00 and XMBH00 > 9999999990)

  and  ((AS_JSFS00='0' and a.jzdh00+0=0) or (AS_JSFS00='1' and a.jzdh00>0) or (AS_JSFS00='-1' and 1=1));





  ---材料的账簿类别就包含材料收入和其他收入
  insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS99,BEIZHU,BEIZH1,BEIZH2,GHKS100,GHKS101,GHKS102,DYID00,GHKS88)
 select ID0000,GHRQ00,c.bh0000,c.xmmc00,BEIZH1,BEIZH2,GHKS100,sum(GHKS101),sum(GHKS102),1,0
 from BM_GHKSTJ a,BM_CWXMDY b ,BM_CWXMBM c
   where a.GHKS99=b.HSXMID and b.bh0000=c.BH0000 and  id0000=ad_ID0000 and DYID00=0 and nvl(c.ZBLB00,'0')='0'
    AND ((as_FYTJ00='-1' AND 1=1) OR (as_FYTJ00='0' AND  c.bh0000 NOT IN ('09','10')) OR (as_FYTJ00='1' AND  c.bh0000 IN  ('09','10')))
 group by ID0000,GHRQ00,c.bh0000,c.xmmc00,BEIZH1,BEIZH2,GHKS100;

 insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZHU,DYID00,GHKS88)values(ad_ID0000,'  ','     ',1,0.1)  ;
 insert into BM_GHKSTJ(ID0000,GHRQ00,BEIZHU,DYID00,GHKS88)values(ad_ID0000,'项目统计：  ','         ',1,0.2)  ;

 insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS99,BEIZHU,BEIZH1,BEIZH2,GHKS100,GHKS101,GHKS102,DYID00,GHKS88)
 select ID0000,'    ',GHKS99,BEIZHU,null,null,null,null,sum(GHKS102),1,2
 from BM_GHKSTJ where id0000=ad_ID0000 and GHKS88=0 and DYID00=1
 group by ID0000,GHKS99,BEIZHU;

insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS99,BEIZHU,BEIZH1,BEIZH2,GHKS100,GHKS101,GHKS102,DYID00,GHKS88)
 select ID0000,'   ',99999999,'合计',null,null,null,null,sum(GHKS102),1,2
 from BM_GHKSTJ where id0000=ad_ID0000 and GHKS88=0 and DYID00=1
 group by ID0000;


 --删除临时数据
 delete BM_GHKSTJ where id0000=ad_ID0000 and DYID00=0;


--commit;
exception
      when others then
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_XMFYCX('||as_KSRQ00||','||as_JSRQ00||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
    rollback;
end;