订单交付及时率
ALTER view TimelyFilingRate_zhx2
as
select
组织名称,
单据类型,
销售订单号,
销售业务员,
客户名称,
订单料号,
订单品名,
客户交期,
生产交期,
订单日期,
订单数量,
出货确认日,
及时交付=case when ( (cast([出货确认日] as integer)=0 or cast([出货确认日] as integer) is Null)
and (cast([客户交期] as integer)
when (cast([出货确认日] as integer)=0) Then '' when (cast([出货确认日] as integer) is Null ) Then ''
when (cast([出货确认日] as integer)-cast([客户交期] as integer))
累计出货数量,
case
when [累计出货数量]=[订单数量] then '是'
when [累计出货数量]
when [累计出货数量]>[订单数量] then '超出'
end as 是否完成
from( ---销售订单信息
select
Orgl.Name 组织名称,
m.Org 组织ID,
D.id 销售订单_单据行ID,
m.ID 销售订单_单据头ID,
D.SrcDoc 销售订单_来源单据头ID,
D.SrcDocLine 销售订单_来源单据行ID,
m.CreatedBy as 销售订单_制单人 ,
T.Name as 单据类型,
m.DescFlexField_PrivateDescSeg1 as 销售订单_客户订单号,
m.DocNo 销售订单号,
SOLP.RequireDate 客户交期,
SOLP.PlanDate 生产交期,
m.BusinessDate 订单日期,
m.OrderBy_Code 销售订单_客户编码,
CC.Name 客户名称,
case
when m.Status=1 then '开立'
when m.Status=2 then '审核中'
when m.Status=3 then '审核'
when m.Status=4 then '自然关闭'
end as 销售订单_状态 ,
hr.Name as 销售业务员,
item.Code 订单料号,
item.Name 订单品名,
item.SPECS 销售订单_料品规格,
D.OrderByQtyTU as 订单数量
from SM_SO m
left join SM_SOLine D on m.ID=D.SO --关联销售订单子表
Left join Base_Organization_Trl Orgl On Orgl.ID=m.Org
left join CBO_ItemMaster item on item.ID=D.ItemInfo_ItemID --关联物料主表
left join CBO_Customer_Trl CC on cc.ID=m.OrderBy_Customer --客户名称
left join SM_SODocType_Trl T on T.ID=m.DocumentType --单据类型
left join CBO_Operators_Trl hr on hr.ID=m.Seller --业务员
Left join SM_SOShipline SOLP On SOLP.SOLine=D.ID --销售订单子行
) So_Order
left join
(
------------出货计划
select
SPL.SrcDoc as 出货计划_来源单据头ID,
SPL.SrcDocLine as 出货计划_来源单据行ID,
SPL.id 出货计划_单据行ID,
sp.ID 出货计划_单据头ID,
DocNo as 出货计划单号,
SP.BusinessDate as 出货计划_单据日期,
case
when SP.Status=1 then '开立'
when SP.Status=2 then '审核中'
when SP.Status=3 then '审核'
when SP.Status=4 then '自然关闭'
end as 出货计划_状态 ,
case
when SP.DescFlexField_PubDescSeg1=10 then '内销'
when SP.DescFlexField_PubDescSeg1=20 then '一般贸易'
w
hen SP.DescFlexField_PubDescSeg1=30 then '进料加工'
when SP.DescFlexField_PubDescSeg1=40 then '免税出口'
end as 贸易类别,
op.Name as 出货计划_计划员,
SPL.PlanQtyTU as 计划数量,
SPL.ShipQtyTU as 累计出货数量
from SM_ShipPlan SP
left join SM_ShipPlanLine SPL on SPL.ShipPlan=SP.ID --出货计划行表
left join CBO_Operators_Trl op on op.ID=SP.Planer --计划员表
) So_Plan on So_Plan.出货计划_来源单据头ID=So_Order.销售订单_单据头ID and So_Plan.出货计划_来源单据行ID=So_Order.销售订单_单据行ID
left join
(
----标准出货单
select
SHL.SrcDocKey as 出货单_来源单据头ID,
SHL.SrcDocLineKey as 出货单_来源单据行ID,
SHL.id 出货单_单据行ID,
sh.ID 出货单_单据头ID,
sh.DocNo as 出货单号,
SH.Createdby 出货单_制单人,
SH.BusinessDate 出货单_单据日期,
SH.ShipConfirmDate as 出货确认日,
case
when SH.Status=1 then '开立'
when SH.Status=2 then '审核中'
when SH.Status=3 then '审核'
when SH.Status=4 then '自然关闭'
end as 出货单状态 ,
SHL.ShipQtyTUAmount as 出货数量,
SHL.LotInfo_LotCode as 批号,
Wh.Name 存储地点,
Bin.Name as 库位
from SM_Ship SH --出货单主表
left join SM_ShipLine SHL on SH.ID=SHL.Ship --出货单行
left join CBO_Wh_Trl Wh on Wh.ID=SHL.WH --存储地点表
left join SM_ShipLineLocation SHLL on SHLL.ShipLine=SHL.ID --出货单行库位表
left join CBO_Bin_Trl Bin on SHLL.Location =Bin.ID --库位表名称表
) So_Ship on So_Ship.出货单_来源单据头ID=So_Plan.出货计划_单据头ID and So_Ship.出货单_来源单据行ID=So_Plan.出货计划_单据行ID
GO