金蝶K3--采购订单--跟进表

select f11.fnumber as 客户代码,d.fsourcebillno as 销售订单,convert(varchar(10),f.fdate,120) as 客户交期,a.fbillno as 采购单号,convert(varchar(10),a.fdate,120) as 订单日期,isnull(g.fnumber,' ') as 成本对象,isnull(g.fmodel,' ') as 成本对象规格,isnull(f.fauxqty,0) as 销售数量,h.fname as 制单,case when b.fauxqty-b.fauxcommitqty>0 then ' ' else '关闭' end as 关闭,case when a.fstatus=0 then ' ' else '已审' end as 审核,case when a.fcancellation=0 then ' ' else '作废' end as 作废,c.fname as 供应商,b.fentryid as 行号,convert(varchar(10),b.fdate,120) as 交货日期,b1.fnumber as 物料代码,b1.fname as 物料名称,b1.fmodel as 规格型号,b2.fname as 单位,isnull(e.fauxnobatchqty,0) as 计划需求,d.fauxqty as 申请数量,b.fauxqty as 订单数量,b.fauxcommitqty as 关联数量,b.fauxstockqty as 采购入库,case when b.fauxqty-b.fauxcommitqty>0 then b.fauxqty-b.fauxcommitqty else 0 end as 欠料数量,isnull(b11.fqty,0) as 即时库存,b.fauxqtyinvoice as 发票数量,b.fauxprice as 单价,b.famount as 金额,b.fauxpricediscount as 含税单价,b.fcess as 税率,b.fallamount as 价税合计,b.fnote as 行备注
from poorder a
left outer join t_user h              on a.fbillerid=h.fuserid
inner join poorderentry b         on a.finterid=b.finterid
left outer join t_icitem b1           on b.fitemid=b1.fitemid
left outer join icinventory b11    on b1.fitemid=b11.fitemid and b1.fdefaultloc=b11.fstockid
left outer join t_measureunit b2 on b.funitid=b2.fmeasureunitid
left outer join t_supplier c          on a.fsupplyid=c.fitemid
left outer join porequestentry d on b.fsourceinterid=d.finterid and b.fsourceentryid=d.fentryid
left outer join icmrpresult e        on d.fplanorderinterid=e.finterid
left outer join seorderentry f      on e.forgsaleinterid=f.finterid and e.forgentyrid=f.fentryid
left outer join seorder f1		on f.finterid=f1.finterid
left outer join t_organization f11 on f1.fcustid=f11.fitemid
left outer join t_icitem g             on f.fitemid=g.fitemid
where d.fsourcetrantype=81 and 1=1 and a.fchangemark=0
union all
select f1.fnumber as 客户代码,d.fsourcebillno as 销售订单,convert(varchar(10),FNeedDateEnd,120) as 客户交期,a.fbillno as 采购单号,convert(varchar(10),a.fdate,120) as 订单日期,isnull(g.fnumber,' ') as 成本对象,isnull(g.fmodel,' ') as 成本对象规格,isnull(f.fauxqty,0) as 销售数量,h.fname as 制单,case when b.fauxqty-b.fauxcommitqty>0 then ' ' else '关闭' end as 关闭,case when a.fstatus=0 then ' ' else '已审' end as 审核,case when a.fcancellation=0 then ' ' else '作废' end as 作废,c.fname as 供应商,b.fentryid as 行号,convert(varchar(10),b.fdate,120) as 交货日期,b1.fnumber as 物料代码,b1.fname as 物料名称,b1.fmodel as 规格型号,b2.fname as 单位,isnull(e.fauxnobatchqty,0) as 计划需求,d.fauxqty as 申请数量,b.fauxqty as 订单数量,b.fauxcommitqty as 关联数量,b.fauxstockqty as 采购入库,case when b.fauxqty-b.fauxcommitqty>0 then b.fauxqty-b.fauxcommitqty else 0 end as 欠料数量,isnull(b11.fqty,0) as 即时库存,b.fauxqtyinvoice as 发票数量,b.fauxprice as 单价,b.famount as 金额,b.fauxpricediscount as 含税单价,b.fcess as 税率,b.fallamount as 价税合计,b.fnote as 行备注
from poorder a
left outer join t_user h              on a.fbillerid=h.fuserid
inner join poorderentry b         on a.finterid=b.finterid
left outer join t_icitem b1           on b.fitemid=b1.fitemid
left outer join icinventory b11    on b1.fitemid=b11.fitemid and b1.fdefaultloc=b11.fstockid
left outer join t_measureunit b2 on b.funitid=b2.fmeasureunitid
left outer join t_supplier c          on a.fsupplyid=c.fitemid
left outer join porequestentry d on b.fsourceinterid=d.finterid and b.fsourceentryid=d.fentryid
left outer join icmrpresult e        on d.fplanorderinterid=e.finterid
left outer join pporderentry f      on e.forgppointerid=f.finterid and e.forgentyrid=f.fentryid
left outer join t_organization f1	 on f.fcustid=f1.fitemid
left outer join t_icitem g             on f.fitemid=g.fitemid
where d.fsourcetrantype=87 and 1=1 and a.fchangemark=0
union all
select null as 客户代码,d.fsourcebillno as 销售订单, null as 客户交期,a.fbillno as 采购单号,convert(varchar(10),a.fdate,120) as 订单日期,isnull(g.fnumber,' ') as 成本对象,isnull(g.fmodel,' ') as 成本对象规格,isnull(f.fauxqty,0) as 销售数量,h.fname as 制单,case when b.fauxqty-b.fauxcommitqty>0 then ' ' else '关闭' end as 关闭,case when a.fstatus=0 then ' ' else '已审' end as 审核,case when a.fcancellation=0 then ' ' else '作废' end as 作废,c.fname as 供应商,b.fentryid as 行号,convert(varchar(10),b.fdate,120) as 交货日期,b1.fnumber as 物料代码,b1.fname as 物料名称,b1.fmodel as 规格型号,b2.fname as 单位,isnull(e.fauxnobatchqty,0) as 计划需求,d.fauxqty as 申请数量,b.fauxqty as 订单数量,b.fauxcommitqty as 关联数量,b.fauxstockqty as 采购入库,case when b.fauxqty-b.fauxcommitqty>0 then b.fauxqty-b.fauxcommitqty else 0 end as 欠料数量,isnull(b11.fqty,0) as 即时库存,b.fauxqtyinvoice as 发票数量,b.fauxprice as 单价,b.famount as 金额,b.fauxpricediscount as 含税单价,b.fcess as 税率,b.fallamount as 价税合计,b.fnote as 行备注
from poorder a
left outer join t_user h              on a.fbillerid=h.fuserid
inner join poorderentry b         on a.finterid=b.finterid
left outer join t_icitem b1           on b.fitemid=b1.fitemid
left outer join icinventory b11    on b1.fitemid=b11.fitemid and b1.fdefaultloc=b11.fstockid
left outer join t_measureunit b2 on b.funitid=b2.fmeasureunitid
left outer join t_supplier c          on a.fsupplyid=c.fitemid
left outer join porequestentry d on b.fsourceinterid=d.finterid and b.fsourceentryid=d.fentryid
left outer join icmrpresult e        on d.fplanorderinterid=e.finterid
left outer join pporderentry f      on e.forgppointerid=f.finterid and e.forgentyrid=f.fentryid
left outer join t_icitem g             on f.fitemid=g.fitemid
where d.fsourcetrantype not in (81,87) and 1=1 and a.fchangemark=0

转载于:https://my.oschina.net/ansenchina/blog/3022662