imp_yunwei/查询当天各模块录入...

当天各模块数据录入
--select * from  SGGMIIP.v_user_model;
select a.user_desc 用户名,bm.fdn 单位,bm.dn 部门,d.serviceType 采购合同类型,
(NVL(b.ks,0) + NVL(c.zyh,0) + NVL(d.cg,0) + NVL(e.xs,0) + NVL(f.CGDD,0) + NVL(g.XSDD,0) + NVL(h.ps,0) + NVL(i.kzh,0) + NVL(j.KZJH,0) 
+ NVL(k.KZSQ,0) + NVL(l.KZDJ,0) + NVL(m.DD,0) + NVL(n.SZDJ,0) + NVL(o.XM,0) + NVL(p.KSSX,0) + NVL(q.XSXY,0) + NVL(r.CGXY,0) 
+ NVL(s.ZC,0) + NVL(t.CC,0) + NVL(u.JG,0) + NVL(v.QT,0) + NVL(w.GB,0) + NVL(x.GD,0) + NVL(y.QZ,0) + NVL(z.HH,0) + NVL(a1.TD,0) 
+ NVL(a2.DZ,0) + NVL(a3.BG,0) + NVL(a4.ZJJH,0) + NVL(a5.SK,0) + NVL(a6.SKTZ,0) + NVL(a7.FK,0) + NVL(a8.FM,0) + NVL(a9.FY,0) 
+ NVL(a10.RZSQ,0) + NVL(a11.RZFK,0) + NVL(a12.RZHK,0) + NVL(a13.DHTZ,0) + NVL(a14.CGRK,0) + NVL(a15.QTRK,0) + NVL(a16.RKTH,0) 
+ NVL(a17.FH,0) + NVL(a18.XSCK,0) + NVL(a19.QTCK,0) + NVL(a20.JKDP,0) + NVL(a21.KPTZ,0) + NVL(a22.JXFP,0) + NVL(a23.FYFP,0) 
+ NVL(a24.XSJS,0) + NVL(a25.XSFYKP,0) + NVL(a26.HZZG,0) + NVL(a27.FYZG,0) + NVL(a28.SPBMSQ,0)+ NVL(a30.zyjh,0) + NVL(a31.kcjg,0))合计录入,
b.ks 客商管理,c.ZYH 资源号,d.cg 采购合同,e.xs 销售合同,
f.CGDD 采购订单,g.XSDD 销售订单,h.ps 合同评审,i.kzh 开证行,j.KZJH 开证计划,k.KZSQ 开证申请,
l.KZDJ 开证登记,m.DD 信用证到单,n.SZDJ 收证登记,o.XM 项目计划书,p.KSSX 客商授信评审,q.XSXY 代理销售协议,
r.CGXY 代理采购协议,s.ZC 租船合同,t.CC 仓储合同,u.JG 加工合同,v.QT 其他合同,w.GB 合同关闭申请 ,x.GD 合同归档,y.QZ 合同签章工作台
,z.HH 换货合同,a1.TD 提单登记,a2.DZ 单证制作,a3.BG 报关单,a4.ZJJH 资金计划,a5.SK 收款,a6.SKTZ 收款调整,
a7.FK 货款付款,a8.FM 非贸付款,a9.FY 费用付款,a10.RZSQ 融资申请,a11.RZFK 融资放款,a12.RZHK 融资还款,a13.DHTZ 到货通知,
a14.CGRK 采购入库,a15.QTRK 其他入库,a16.RKTH 入库退回,a17.FH 放货申请,a18.XSCK 销售出库,a19.QTCK 其他出库,
a20.JKDP 进口到票,a21.KPTZ 开票通知,a22.JXFP 进项发票登记,a23.FYFP 费用发票登记,a24.XSJS 销售结算开票,a25.XSFYKP 销售费用结算开票
,a26.HZZG 货值暂估,a27.FYZG 费用暂估,a28.SPBMSQ 商品编码申请,a30.zyjh 作业计划,a31.kcjg 库存加工
  from SGGMIIP.sys_user a 
  LEFT JOIN SGGMIIP.SYS_USER_DEPARTMENT sud on a.id = sud.user_id
  LEFT JOIN (select fsd.dept_name as fdn,fsd.dept_code as fdc,sd.dept_name as dn,sd.dept_code as dc,sd.id from SGGMIIP.SYS_DEPARTMENT sd LEFT JOIN SGGMIIP.SYS_DEPARTMENT fsd
on sd.f_id = fsd.dept_code)bm on bm.id = sud.dept_id
 left join  
(select create_by,count(*)ks from SGGMIIP.custctrl  --客商
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) b on a.USER_CODE=b.create_by

left join  
(select create_by,count(*) zyh from SGGMIIP.resourcectrl  --资源号
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) c on a.USER_CODE=c.create_by

left join  
(select create_by,serviceType,count(*)cg from SGGMIIP.purchaseContract --采购合同
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
and nvl(remark,'') != '期初导入-采购'
group by create_by ,serviceType) d on a.USER_CODE=d.create_by

left join  
(select create_by,count(*)xs from SGGMIIP.salesContract --销售合同
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
and nvl(remark,'') != '期初'
group by create_by ) e on a.USER_CODE=e.create_by

left join  
(select create_by,count(*)cgdd from SGGMIIP.purchaseOrder  --采购订单
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) f on a.USER_CODE=f.create_by

left join  
(select create_by,count(*) xsdd from SGGMIIP.salesOrder  --销售订单
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) g on a.USER_CODE=g.create_by

left join  
(select create_by,count(*)ps from SGGMIIP.ContractReview --合同评审
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) h on a.USER_CODE=h.create_by

left join  
(select create_by,count(*)kzh from SGGMIIP.LcBank --开证行
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) i on a.USER_CODE=i.create_by

left join  
(select create_by,count(*)kzjh from SGGMIIP.lcplan --开证计划
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) j on a.USER_CODE=j.create_by

left join  
(select create_by,count(*)kzsq from SGGMIIP.lcApplication --开证申请
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) k on a.USER_CODE=k.create_by

left join  
(select create_by,count(*)kzdj from SGGMIIP.Lcctrl --开证登记
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) l on a.USER_CODE=l.create_by

left join  
(select create_by,count(*)dd from SGGMIIP.lcrctrl --信用证到单
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) m on a.USER_CODE=M.create_by

left join  
(select create_by,count(*)szdj from SGGMIIP.breceiptctrl--收证登记
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid =20 and create_time>='2025-12-04 17:00:01'
group by create_by ) N on a.USER_CODE=n.create_by

left join  
(select create_by,count(*)xm from SGGMIIP.ProjectPlan --项目计划书
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) o on a.USER_CODE=o.create_by

left join  
(select create_by,count(*)kssx from 	SGGMIIP.CtCredReview--客商授信评审
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) p on a.USER_CODE=p.create_by

left join  
(select create_by,count(*)xsxy from SGGMIIP.ExpAgencyAgt --代理销售协议
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) q on a.USER_CODE=q.create_by

left join  
(select create_by,count(*)cgxy from 	SGGMIIP.ImpAgencyAgt--代理采购协议
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) r on a.USER_CODE=r.create_by

left join  
(select create_by,count(*)zc from SGGMIIP.shippingContract --租船合同
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) s on a.USER_CODE=s.create_by

left join  
(select create_by,count(*)cc from 	SGGMIIP.whosContract--仓储合同
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) t on a.USER_CODE=t.create_by

left join  
(select create_by,count(*)jg from 	SGGMIIP.processContract --加工合同
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) u on a.USER_CODE=u.create_by

left join  
(select create_by,count(*)qt from 	SGGMIIP.otherTContract--其他合同
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) v on a.USER_CODE=v.create_by

left join  
(select create_by,count(*)gb from 	SGGMIIP.contrCloseReq --合同关闭
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) w on a.USER_CODE=w.create_by

left join  
(select create_by,count(*)gd from 	SGGMIIP.contractArchiving--合同归档
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) x on a.USER_CODE=x.create_by

left join  
(select create_by,count(*)qz from 	SGGMIIP.contractSignature --合同签章
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) y on a.USER_CODE=y.create_by

left join  
(select create_by,count(*)hh from 	SGGMIIP.exchangeContract--换货合同
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) z on a.USER_CODE=z.create_by

left join  
(select create_by,count(*)td from 	SGGMIIP.blReg --提单登记
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a1 on a.USER_CODE=a1.create_by

left join  
(select create_by,count(*)dz from 	SGGMIIP.invList--单证制作
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='11' and create_time>='2025-12-04 17:00:01'
group by create_by ) a2 on a.USER_CODE=a2.create_by

left join  
(select create_by,count(*)bg from 	SGGMIIP.invList --报关
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='130' and create_time>='2025-12-04 17:00:01'
group by create_by ) a3 on a.USER_CODE=a3.create_by

left join  
(select create_by,count(*)zjjh from 	SGGMIIP.FundPlan--资金计划
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a4 on a.USER_CODE=a4.create_by

left join  
(select create_by,count(*)sk from 	SGGMIIP.breceiptctrl --收款
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='19' and create_time>='2025-12-04 17:00:01'
group by create_by ) a5 on a.USER_CODE=a5.create_by

left join  
(select create_by,count(*)sktz from SGGMIIP.ReceiptAdjustLog --收款调整申请
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a6 on a.USER_CODE=a6.create_by

left join  
(select create_by,count(*)fk from 	SGGMIIP.paymgrctrl --货款付款
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='15' and create_time>='2025-12-04 17:00:01'
group by create_by ) a7 on a.USER_CODE=a7.create_by

left join  
(select create_by,count(*)fm from SGGMIIP.paymgrctrl --非贸货款
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>''  and fmodalid='17' and create_time>='2025-12-04 17:00:01'
group by create_by ) a8 on a.USER_CODE=a8.create_by

left join  
(select create_by,count(*)fy from 	SGGMIIP.paymgrctrl --费用付款
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='16' and create_time>='2025-12-04 17:00:01'
group by create_by ) a9 on a.USER_CODE=a9.create_by

left join  
(select create_by,count(*)rzsq from SGGMIIP.financingReq --融资申请
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a10 on a.USER_CODE=a10.create_by

left join  
(select create_by,count(*)rzfk from 	SGGMIIP.loanReg --融资放款登记
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a11 on a.USER_CODE=a11.create_by

left join  
(select create_by,count(*)rzhk from SGGMIIP.repayReq --融资还款申请
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a12 on a.USER_CODE=a12.create_by

left join  
(select create_by,count(*)dhtz from 	SGGMIIP.grnctrl --到货通知
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a13 on a.USER_CODE=a13.create_by

left join  
(select create_by,count(*)cgrk from SGGMIIP.mdkcctrl --采购入库
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='22' and create_time>='2025-12-04 17:00:01'
group by create_by ) a14 on a.USER_CODE=a14.create_by

left join  
(select create_by,count(*)qtrk from 	SGGMIIP.mdkcctrl --其他入库
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='27' and create_time>='2025-12-04 17:00:01'
group by create_by ) a15 on a.USER_CODE=a15.create_by

left join  
(select create_by,count(*)rkth from SGGMIIP.mdkcctrl --入库退回
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='86' and create_time>='2025-12-04 17:00:01'
group by create_by ) a16 on a.USER_CODE=a16.create_by

left join  
(select create_by,count(*)fh from 	SGGMIIP.delivNotiReq --放货申请
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a17 on a.USER_CODE=a17.create_by

left join  
(select create_by,count(*)xsck from SGGMIIP.mdkcctrl --销售出库
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='24' and create_time>='2025-12-04 17:00:01'
group by create_by ) a18 on a.USER_CODE=a18.create_by

left join  
(select create_by,count(*)qtck from 	SGGMIIP.mdkcctrl --其他出库
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='28' and create_time>='2025-12-04 17:00:01'
group by create_by ) a19 on a.USER_CODE=a19.create_by

left join  
(select create_by,count(*)jkdp from SGGMIIP.purMultiSettle --进口到票
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a20 on a.USER_CODE=a20.create_by

left join  
(select create_by,count(*)kptz from 	SGGMIIP.expPurInvNoti --开票通知
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a21 on a.USER_CODE=a21.create_by

left join  
(select create_by,count(*)jxfp from SGGMIIP.inputInvReg --进项发票登记
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a22 on a.USER_CODE=a22.create_by

left join  
(select create_by,count(*)fyfp from 	SGGMIIP.feelInvReq --费用发票登记
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a23 on a.USER_CODE=a23.create_by

left join  
(select create_by,count(*)xsjs from SGGMIIP.saleSettle --销售结算开票
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='36' and create_time>='2025-12-04 17:00:01'
group by create_by ) a24 on a.USER_CODE=a24.create_by

left join  
(select create_by,count(*)xsfykp from 	SGGMIIP.saleSettle --销售费用结算开票
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and fmodalid='333' and create_time>='2025-12-04 17:00:01'
group by create_by ) a25 on a.USER_CODE=a25.create_by

left join  
(select create_by,count(*)hzzg from SGGMIIP.provIn --货值暂估
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a26 on a.USER_CODE=a26.create_by

left join  
(select create_by,count(*)fyzg from 	SGGMIIP.estFeeInv --费用暂估
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a27 on a.USER_CODE=a27.create_by

left join  
(select create_by,count(*)spbmsq from SGGMIIP.prodNoReq --商品编码申请
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
group by create_by ) a28 on a.USER_CODE=a28.create_by

left join 
(select create_by,count(*)zyjh from SGGMIIP.mdkcctrl	--作业计划
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
and fmodalid='155'
group by create_by) a30 on a.USER_CODE=a30.create_by

left join 
(select create_by,count(*)kcjg from SGGMIIP.mdkcctrl	--库存加工
where nvl(del_flag,0)<>1
and nvl(create_by,'')<>'' and create_time>='2025-12-04 17:00:01'
and fmodalid='168'
group by create_by) a31 on a.USER_CODE=a31.create_by

where (NVL(b.ks,0)+
NVL(c.zyh,0)+
NVL(d.cg,0)+
NVL(e.xs,0)+
NVL(f.cgdd,0)+
NVL(g.xsdd,0)+
NVL(h.ps,0)+
NVL(i.kzh,0)+
NVL(j.kzjh,0)+
NVL(k.kzsq,0)+
NVL(l.kzdj,0)+
NVL(m.dd,0)+
NVL(n.szdj,0)+
NVL(o.xm,0)+
NVL(p.kssx,0)+
NVL(q.xsxy,0)+
NVL(r.cgxy,0)+
NVL(s.zc,0)+
NVL(t.cc,0)+
NVL(u.jg,0)+
NVL(v.qt,0)+
NVL(w.gb,0)+
NVL(x.gd,0)+
NVL(y.qz,0)+
NVL(z.hh,0)+
NVL(a1.td,0)+
NVL(a2.dz,0)+
NVL(a3.bg,0)+
NVL(a4.zjjh,0)+
NVL(a5.sk,0)+
NVL(a6.sktz,0)+
NVL(a7.fk,0)+
NVL(a8.fm,0)+
NVL(a9.fy,0)+
NVL(a10.rzsq,0)+
NVL(a11.rzfk,0)+
NVL(a12.rzhk,0)+
NVL(a13.dhtz,0)+
NVL(a14.cgrk,0)+
NVL(a15.qtrk,0)+
NVL(a16.rkth,0)+
NVL(a17.fh,0)+
NVL(a18.xsck,0)+
NVL(a19.qtck,0)+
NVL(a20.jkdp,0)+
NVL(a21.kptz,0)+
NVL(a22.jxfp,0)+
NVL(a23.fyfp,0)+
NVL(a24.xsjs,0)+
NVL(a25.xsfykp,0)+
NVL(a26.hzzg,0)+
NVL(a27.fyzg,0)+
NVL(a28.spbmsq,0)+
NVL(a30.zyjh,0)+
NVL(a31.kcjg,0))>0

order by USER_CODE