金蝶K/3 WISE 12.3订单跟踪SQL报表

金蝶K3一直缺少完整的跟踪报表，所以我们开发了一张完整的跟踪报表，通过查询分析工具直接生成。

代码（WIN2008 R2+SQL 2008 R2环境，K3 WISE 12.3）：

select t1.fname1 客户,t1.fname2 业务员,t1.f_102 款号,t1.fbillno 订单号,t1.fdate 订单日期,t1.fqty 订单数量,t5.fqty 生产数量,t10.fnumber BOM材料代码,t10.fname BOM材料名称,t6.fnumber 投料单材料代码,t6.fqty 投料单数量,t7.fqty 领料单数量,t7.soutfprice 领料单价,t7.soutfamount 领料金额,
t2.fdate 申请日期,t2.SEEfbillno 申请单号,t2.fnumber 申请物料代码,t2.fqty 申请数量,t3.fdate 采购日期,t3.sefbillno 采购订单号,t3.fqty 采购订单数量,t3.fprice 采购单价,t3.famount 采购金额,t4.fdate 入库日期,t4.fbillno 入库单号,t4.fqty 入库数量,t8.cpfdate 成品入库日期,t8.fqty 产品入库数,t8.cpfprice 入库单价,t8.cpfamount 入库金额,
t9.fqty 出库数量,t9.fqtyinvoice 开票数量 FROM
(select t5.f_102,t3.fname fname1,t4.fname fname2,t1.fbillno,sum(t2.fqty) fqty,t1.fdate,t1.finterid  from seorder t1
inner join seorderentry t2 on t1.finterid=t2.finterid
inner join t_organization t3 on t1.fcustid=t3.fitemid
inner join t_emp t4 on t1.fempid=t4.fitemid
inner join t_icitem t5 on t2.fitemid=t5.fitemid where t1.fcancellation=0
group by t5.f_102,t3.fname,t4.fname,t1.fbillno,t1.fdate,t1.finterid) t1
--生产任务单
left join (select t2.f_102,t1.forderinterid,sum(t1.fqty) fqty from icmo t1
inner join t_icitem t2 on t1.fitemid=t2.fitemid
group by t2.f_102,t1.forderinterid) t5 on t1.f_102=t5.f_102 and t1.finterid=t5.forderinterid
--产品入库单
left join (select t3.f_102,t2.fentryselfa0240,sum(fqty) fqty,t1.fdate cpfdate,t2.fprice cpfprice,t2.famount cpfamount from icstockbill t1
inner join icstockbillentry t2 on t1.finterid=t2.finterid
inner join t_icitem t3 on t2.fitemid=t3.fitemid where t1.ftrantype=2
group by t3.f_102,t2.fentryselfa0240,t2.fprice,t2.famount,t1.fdate) t8 on t1.f_102=t8.f_102 and t1.fbillno=t8.fentryselfa0240
--销售出库
left join (select t3.f_102,t2.forderbillno,sum(fqty) fqty,sum(fqtyinvoice) fqtyinvoice from icstockbill t1
inner join icstockbillentry t2 on t1.finterid=t2.finterid
inner join t_icitem t3 on t2.fitemid=t3.fitemid where t1.ftrantype=21
group by t3.f_102,t2.forderbillno) t9 on t1.f_102=t9.f_102 and t1.fbillno=t9.forderbillno
--BOM单
left join (select distinct t3.f_102,t4.fnumber,t4.fname from icbom t1
inner join ICBOMCHILD t2 on t1.finterid=t2.finterid
inner join t_icitem t3 on t3.fitemid=t1.fitemid
inner join t_icitem t4 on t2.fitemid=t4.fitemid) t10 on t10.f_102=t1.f_102
--投料单
left join (select t3.f_102,t1.forderbillno,t4.fnumber,t4.fname,sum(t2.fqtymust) fqty from ppbom t1
inner join ppbomentry t2 on t1.finterid=t2.finterid
inner join t_icitem t3 on t1.fitemid=t3.fitemid
inner join t_icitem t4 on t2.fitemid=t4.fitemid
group by t3.f_102,t1.forderbillno,t4.fnumber,t4.fname) t6 on t1.f_102=t6.f_102 and t1.fbillno=t6.forderbillno and t6.fnumber=t10.fnumber
--领料单
left join (select fentryselfb0453,fentryselfb0454,t3.fnumber,sum(fqty) fqty,t2.fprice soutfprice,t2.famount soutfamount from icstockbill t1
inner join icstockbillentry t2 on t1.finterid=t2.finterid
inner join t_icitem t3 on t2.fitemid=t3.fitemid where t1.ftrantype=24
group by fentryselfb0453,fentryselfb0454,t3.fnumber,t2.fprice,t2.famount) t7 on t1.f_102=t7.fentryselfb0453 and t1.fbillno=t7.fentryselfb0454 and t6.fnumber=t7.fnumber
--采购申请
left join (select t1.fheadselfp0136,t2.fentryselfp0137,t3.fnumber,t3.fname,sum(t2.fqty) fqty,t1.fdate fdate,t1.fbillno SEEfbillno from porequest t1
inner join porequestentry t2 on t1.finterid=t2.finterid
inner join t_icitem t3 on t2.fitemid=t3.fitemid
group by t1.fheadselfp0136,t2.fentryselfp0137,t3.fnumber,t3.fname,t1.fdate,t1.fbillno) t2 on t1.fbillno=t2.fheadselfp0136 and t1.f_102=t2.fentryselfp0137 and t10.fnumber=t2.fnumber
--采购订单
left join (select t2.fentryselfp0270,t2.fentryselfp0269,t3.fnumber,t3.fname,sum(t2.fqty) fqty,t2.fprice fprice,t2.famount famount,t2.fdate fdate,t1.fbillno sefbillno from poorder t1
inner join poorderentry t2 on t1.finterid=t2.finterid
inner join t_icitem t3 on t2.fitemid=t3.fitemid
group by t2.fentryselfp0270,t2.fentryselfp0269,t3.fnumber,t3.fname,t2.fprice,t2.famount,t2.fdate,t1.fbillno) t3 on t1.fbillno=t3.fentryselfp0270 and t1.f_102=t3.fentryselfp0269 and t10.fnumber=t3.fnumber
--外购入库单
left join (select t2.fentryselfa0158,t2.fentryselfa0159,t1.fdate,t3.fnumber,t3.fname,t1.fdate fdate1,t1.fbillno fbillno,sum(t2.fqty) fqty from icstockbill t1
inner join icstockbillentry t2 on t1.finterid=t2.finterid
inner join t_icitem t3 on t3.fitemid=t2.fitemid
where t1.ftrantype=1
group by t2.fentryselfa0158,t2.fentryselfa0159,t1.fdate,t3.fnumber,t3.fname,t1.fdate,t1.fbillno) t4 on t1.fbillno=t4.fentryselfa0159 and t1.f_102=t4.fentryselfa0158 and t10.fnumber=t4.fnumber
where t1.f_102 like ‘%*kuanghao*%‘ and t1.fbillno like ‘%*FBillNoCommon*%‘
order by t1.fdate

本文出自 “小企业小网管” 博客，请务必保留此出处http://t4513.blog.51cto.com/773180/1917723

金蝶K/3 WISE 12.3订单跟踪SQL报表

标签：业务员   订单号   领料单   erp   开发   金蝶