create or replace view zzl_kcsf as
(
select ic_openbal_b.cprojectid,
       bd_project.project_name,
       bd_project.project_code,
       bd_material.materialtype,
       org_stockorg.code,
       org_stockorg.name gs,
       bd_stordoc.name ck,
       bd_stordoc.code ckbm,
       '期初余额' lb,
       ic_openbal_h.vbillcode djh,
       substr(ic_openbal_h.dbilldate, 0, 10) rq,
       ic_openbal_h.vdef4 df,
       ic_openbal_h.cwhsmanagerid ry,
       ic_openbal_h.vdef6 xm,
       bd_marbasclass.code flbm,
       bd_marbasclass.name wlfl,
       bd_material.code wlbm,
       bd_material.name wl,
       bd_material.materialspec wlgg,
       bd_measdoc.name dw,
       ic_openbal_b.vbdef1 sgdh,
       ic_openbal_b.vbdef3 cbdx,
       ic_openbal_b.vbdef4 sybw,
       ic_openbal_b.ncostprice dj,
       ic_openbal_b.nassistnum sl,
       ic_openbal_b.ncostmny rkje,
       0 ckje,
       ic_openbal_b.vnotebody zy,
       ic_openbal_h.vdef1 cght,
       ic_openbal_h.vdef7 jzht,
       ic_openbal_h.vdef3 htbm
  from ic_openbal_h ic_openbal_h
  left join ic_openbal_b ic_openbal_b on ic_openbal_h.cgeneralhid =
                                         ic_openbal_b.cgeneralhid
  left join org_stockorg org_stockorg on ic_openbal_h.pk_org =
                                         org_stockorg.pk_stockorg
  left join bd_stordoc bd_stordoc on ic_openbal_h.cwarehouseid =
                                     bd_stordoc.pk_stordoc
  left join bd_material bd_material on ic_openbal_b.cmaterialvid =
                                       bd_material.pk_material
  left join bd_marbasclass bd_marbasclass on bd_material.pk_marbasclass =
                                             bd_marbasclass.pk_marbasclass
  left join bd_measdoc bd_measdoc on bd_material.pk_measdoc =bd_measdoc.pk_measdoc
  left join bd_project bd_project on ic_openbal_b.cprojectid =
                                     bd_project.pk_project
 where 1 = 1
   and ic_openbal_h.dr = 0
   and ic_openbal_b.dr = 0
   and org_stockorg.code like '103%'
      --and f.code like '7%'
   and bd_material.code like '7%'
union all
select ic_purchasein_b.cprojectid,
       bd_project.project_name,
       bd_project.project_code,
       bd_material.materialtype,
       org_stockorg.code,
       org_stockorg.name gs,
       bd_stordoc.name ck,
       bd_stordoc.code ckbm,
       '采购入库' lb,
       ic_purchaseinzzl_kcsf_h.vbillcode djh,
       substr(ic_purchasein_h.dbilldate, 0, 10) rq,
       ic_purchasein_h.cvendorid df,
       ic_purchasein_h.cwhsmanagerid ry,
       ic_purchasein_h.vdef6 xm,
       bd_marbasclass.code flbm,
       bd_marbasclass.name wlfl,
       bd_material.code wlbm,
       bd_material.name wl,
       bd_material.materialspec wlgg,
       bd_measdoc.name dw,
       ic_purchasein_b.vbdef1 sgdh,
       ic_purchasein_b.vbdef3 cbdx,
       ic_purchasein_b.vbdef4 sybw,
       to_number(ic_purchasein_b.vbdef12) dj,
       ic_purchasein_b.nassistnum sl,
       ic_purchasein_b.norigtaxmny rkje,
       0 ckje,
       ic_purchasein_b.vnotebody zy,
       ic_purchasein_h.vdef1 cght,
       ic_purchasein_h.vdef7 jzht,
       ic_purchasein_h.vdef3 htbm
  from ic_purchasein_h ic_purchasein_h
  left join ic_purchasein_b ic_purchasein_b on ic_purchasein_h.cgeneralhid =
                                               ic_purchasein_b.cgeneralhid
  left join org_stockorg org_stockorg on ic_purchasein_h.pk_org =
                                         org_stockorg.pk_stockorg
  left join bd_stordoc bd_stordoc on ic_purchasein_h.cwarehouseid =
                                     bd_stordoc.pk_stordoc
  left join bd_material bd_material on ic_purchasein_b.cmaterialvid =
                                       bd_material.pk_material
  left join bd_marbasclass bd_marbasclass on bd_material.pk_marbasclass =
                                             bd_marbasclass.pk_marbasclass
  left join bd_measdoc bd_measdoc on bd_material.pk_measdoc =
                                     bd_measdoc.pk_measdoc
  left join bd_project bd_project on ic_purchasein_b.cprojectid =
                                     bd_project.pk_project
  left join bd_defdoc bd_defdoc on ic_purchasein_h.vdef15 =
                                   bd_defdoc.pk_defdoc
 where 1 = 1
   and ic_purchasein_h.dr = 0
   and ic_purchasein_b.dr = 0
   and org_stockorg.code like '103%'
      --and f.code like '7%'
   and bd_material.code like '7%'
union all
select ic_generalin_b.cprojectid,
       bd_project.project_name,
       bd_project.project_code,
       bd_material.materialtype,
       org_stockorg.code,
       org_stockorg.name gs,
       bd_stordoc.name ck,
       bd_stordoc.code ckbm,
       '普通入库' lb,
       ic_generalin_h.vbillcode djh,
       substr(ic_generalin_h.dbilldate, 0, 10) rq,
       ic_generalin_h.vdef4 df,
       ic_generalin_h.cwhsmanagerid ry,
       ic_generalin_h.vdef6 xm,
       bd_marbasclass.code flbm,
       bd_marbasclass.name wlfl,
       bd_material.code wlbm,
       bd_material.name wl,
       bd_material.materialspec wlgg,
       bd_measdoc.name dw,
       ic_generalin_b.vbdef1 sgdh,
       ic_generalin_b.vbdef3 cbdx,
       ic_generalin_b.vbdef4 sybw,
       ic_generalin_b.ncostprice dj,
       ic_generalin_b.nassistnum sl,
       ic_generalin_b.ncostmny rkje,
       0 ckje,
       ic_generalin_b.vnotebody zy,
       ic_generalin_h.vdef1 cght,
       ic_generalin_h.vdef7 jzht,
       ic_generalin_h.vdef3 htbm
  from ic_generalin_h ic_generalin_h
  left join ic_generalin_b ic_generalin_b on ic_generalin_h.cgeneralhid =
                                             ic_generalin_b.cgeneralhid
  left join org_stockorg org_stockorg on ic_generalin_h.pk_org =
                                         org_stockorg.pk_stockorg
  left join bd_stordoc bd_stordoc on ic_generalin_h.cwarehouseid =
                                     bd_stordoc.pk_stordoc
  left join bd_material bd_material on ic_generalin_b.cmaterialvid =
                                       bd_material.pk_material
  left join bd_marbasclass bd_marbasclass on bd_material.pk_marbasclass =
                                             bd_marbasclass.pk_marbasclass
  left join bd_measdoc bd_measdoc on bd_material.pk_measdoc =
                                     bd_measdoc.pk_measdoc
  left join bd_project bd_project on ic_generalin_b.cprojectid =
                                     bd_project.pk_project
  left join bd_defdoc bd_defdoc on ic_generalin_h.vdef15 =
                                   bd_defdoc.pk_defdoc
 where 1 = 1
   and ic_generalin_h.dr = 0
   and ic_generalin_b.dr = 0
   and org_stockorg.code like '103%'
      --and f.code like '7%'
   and bd_material.code like '7%'
--and g.code = '701002007009'
union all
select ia_i9bill_b.cprojectid,
       bd_project.project_name,
       bd_project.project_code,
       bd_material.materialtype,
       org_stockorg.code,
       org_stockorg.name gs,
       bd_stordoc.name ck,
       bd_stordoc.code ckbm,
       '入库调整' lb,
       ia_i9bill.vbillcode djh,
       substr(ia_i9bill.dbilldate, 0, 10) rq,
       ia_i9bill.cvendorid df,
       ia_i9bill.cstordocmanid ry,
       ia_i9bill.vdef6 xm,
       bd_marbasclass.code flbm,
       bd_marbasclass.name wlfl,
       bd_material.code wlbm,
       bd_material.name wl,
       bd_material.materialspec wlgg,
       bd_measdoc.name dw,
       ia_i9bill_b.vbdef1 sgdh,
       ia_i9bill_b.vbdef3 cbdx,
       ia_i9bill_b.vbdef4 sybw,
       0 dj,
       0 sl,
       ia_i9bill_b.nmny rkje,
       0 ckje,
       '入库调整' zy,
       ia_i9bill.vdef1 cght,
       ia_i9bill.vdef7 jzht,
       ia_i9bill.vdef3 htbm
  from ia_i9bill ia_i9bill
  left join ia_i9bill_b ia_i9bill_b on ia_i9bill.cbillid =
                                       ia_i9bill_b.cbillid
  left join org_stockorg org_stockorg on ia_i9bill.cstockorgid =
                                         org_stockorg.pk_stockorg
  left join bd_stordoc bd_stordoc on ia_i9bill.cstordocid =
                                     bd_stordoc.pk_stordoc
  left join bd_material bd_material on ia_i9bill_b.cinventoryvid =
                                       bd_material.pk_material
  left join bd_marbasclass bd_marbasclass on bd_material.pk_marbasclass =
                                             bd_marbasclass.pk_marbasclass
  left join bd_measdoc bd_measdoc on bd_material.pk_measdoc =
                                     bd_measdoc.pk_measdoc
  left join bd_project bd_project on ia_i9bill_b.cprojectid =
                                     bd_project.pk_project
  left join bd_defdoc bd_defdoc on ia_i9bill.vdef15 = bd_defdoc.pk_defdoc
 where 1 = 1
   and ia_i9bill.dr = 0
   and ia_i9bill_b.dr = 0
   and org_stockorg.code like '103%'
      --and f.code like '7%'
   and bd_material.code like '7%'
union all
select ic_material_b.cprojectid,
       bd_project.project_name,
       bd_project.project_code,
       bd_material.materialtype,
       org_stockorg.code,
       org_stockorg.name gs,
       bd_stordoc.name ck,
       bd_stordoc.code ckbm,
       '材料出库' lb,
       ic_material_h.vbillcode djh,
       substr(ic_material_h.dbilldate, 0, 10) rq,
       ic_material_h.vdef4 df,
       ic_material_h.cbizid ry,
       ic_material_h.vdef6 xm,
       bd_marbasclass.code flbm,
       bd_marbasclass.name wlfl,
       bd_material.code wlbm,
       bd_material.name wl,
       bd_material.materialspec wlgg,
       bd_measdoc.name dw,
       ic_material_b.vbdef1 sgdh,
       ic_material_b.vbdef3 cbdx,
       ic_material_b.vbdef4 sybw,
       ncostprice dj,
       ic_material_b.nassistnum sl,
       0 rkje,
       ic_material_b.ncostmny ckje,
       ic_material_b.vnotebody zy,
       ic_material_h.vdef1 cght,
       ic_material_h.vdef7 jzht,
       ic_material_h.vdef3 htbm
  from ic_material_h ic_material_h
  left join ic_material_b ic_material_b on ic_material_h.cgeneralhid =
                                           ic_material_b.cgeneralhid
  left join org_stockorg org_stockorg on ic_material_h.pk_org =
                                         org_stockorg.pk_stockorg
  left join bd_stordoc bd_stordoc on ic_material_h.cwarehouseid =
                                     bd_stordoc.pk_stordoc
  left join bd_material bd_material on ic_material_b.cmaterialvid =
                                       bd_material.pk_material
  left join bd_marbasclass bd_marbasclass on bd_material.pk_marbasclass =
                                             bd_marbasclass.pk_marbasclass
  left join bd_measdoc bd_measdoc on bd_material.pk_measdoc =
                                     bd_measdoc.pk_measdoc
  left join bd_project bd_project on ic_material_b.cprojectid =
                                     bd_project.pk_project
  left join bd_defdoc bd_defdoc on ic_material_h.vdef15 =
                                   bd_defdoc.pk_defdoc
 where 1 = 1
   and ic_material_h.dr = 0
   and ic_material_b.dr = 0
   and org_stockorg.code like '103%'
      --and f.code like '7%'
   and bd_material.code like '7%'
union all
select ic_generalout_b.cprojectid,
       bd_project.project_name,
       bd_project.project_code,
       bd_material.materialtype,
       org_stockorg.code,
       org_stockorg.name gs,
       bd_stordoc.name ck,
       bd_stordoc.code ckbm,
       '普通出库' lb,
       ic_generalout_h.vbillcode djh,
       substr(ic_generalout_h.dbilldate, 0, 10) rq,
       ic_generalout_h.vdef4 df,
       ic_generalout_h.cbizid ry,
       ic_generalout_h.vdef6 xm,
       bd_marbasclass.code flbm,
       bd_marbasclass.name wlfl,
       bd_material.code wlbm,
       bd_material.name wl,
       bd_material.materialspec wlgg,
       bd_measdoc.name dw,
       ic_generalout_b.vbdef1 sgdh,
       ic_generalout_b.vbdef3 cbdx,
       ic_generalout_b.vbdef4 sybw,
       ncostprice dj,
       ic_generalout_b.nassistnum sl,
       0 rkje,
       ic_generalout_b.ncostmny ckje,
       ic_generalout_b.vnotebody zy,
       ic_generalout_h.vdef1 cght,
       ic_generalout_h.vdef7 jzht,
       ic_generalout_h.vdef3 htbm
  from ic_generalout_h ic_generalout_h
  left join ic_generalout_b ic_generalout_b on ic_generalout_h.cgeneralhid =
                                               ic_generalout_b.cgeneralhid
  left join org_stockorg org_stockorg on ic_generalout_h.pk_org =
                                         org_stockorg.pk_stockorg
  left join bd_stordoc bd_stordoc on ic_generalout_h.cwarehouseid =
                                     bd_stordoc.pk_stordoc
  left join bd_material bd_material on ic_generalout_b.cmaterialvid =
                                       bd_material.pk_material
  left join bd_marbasclass bd_marbasclass on bd_material.pk_marbasclass =
                                             bd_marbasclass.pk_marbasclass
  left join bd_measdoc bd_measdoc on bd_material.pk_measdoc =
                                     bd_measdoc.pk_measdoc
  left join bd_project bd_project on ic_generalout_b.cprojectid =
                                     bd_project.pk_project
  left join bd_defdoc bd_defdoc on ic_generalout_h.vdef15 =
                                   bd_defdoc.pk_defdoc
 where 1 = 1
   and ic_generalout_h.dr = 0
   and ic_generalout_b.dr = 0
      --and c.code like '103%'f
      --and f.code like '7%'
   and bd_material.code like '7%'
union all
select ia_iabill_b.cprojectid,
       bd_project.project_name,
       bd_project.project_code,
       bd_material.materialtype,
       org_stockorg.code,
       org_stockorg.name gs,
       bd_stordoc.name ck,
       bd_stordoc.code ckbm,
       '出库调整' lb,
       ia_iabill.vbillcode djh,
       substr(ia_iabill.dbilldate, 0, 10) rq,
       ia_iabill.vdef4 df,
       ia_iabill.cstordocmanid ry,
       ia_iabill.vdef6 xm,
       bd_marbasclass.code flbm,
       bd_marbasclass.name wlfl,
       bd_material.code wlbm,
       bd_material.name wl,
       bd_material.materialspec wlgg,
       bd_measdoc.name dw,
       ia_iabill_b.vbdef1 sgdh,
       ia_iabill_b.vbdef3 cbdx,
       ia_iabill_b.vbdef4 sybw,
       0 dj,
       0 sl,
       0 rkje,
       ia_iabill_b.nmny ckje,
       '出库调整' zy,
       ia_iabill.vdef1 cght,
       ia_iabill.vdef7 jzht,
       ia_iabill.vdef3 htbm
  from ia_iabill ia_iabill
  left join ia_iabill_b ia_iabill_b on ia_iabill.cbillid =
                                       ia_iabill_b.cbillid
  left join org_stockorg org_stockorg on ia_iabill.cstockorgid =
                                         org_stockorg.pk_stockorg
  left join bd_stordoc bd_stordoc on ia_iabill.cstordocid =
                                     bd_stordoc.pk_stordoc
  left join bd_material bd_material on ia_iabill_b.cinventoryvid =
                                       bd_material.pk_material
  left join bd_marbasclass bd_marbasclass on bd_material.pk_marbasclass =
                                             bd_marbasclass.pk_marbasclass
  left join bd_measdoc bd_measdoc on bd_material.pk_measdoc =
                                     bd_measdoc.pk_measdoc
  left join bd_project bd_project on ia_iabill_b.cprojectid =
                                     bd_project.pk_project
  left join bd_defdoc bd_defdoc on ia_iabill.vdef15 = bd_defdoc.pk_defdoc
 where 1 = 1
   and ia_iabill.dr = 0
   and ia_iabill_b.dr = 0
   and org_stockorg.code like '103%'
      --and f.code like '7%'
   and bd_material.code like '7%'
   )
;
