预留关系表sql

WITH YLCX AS (

SELECT

YL.业务数量,

YL.预留数量,

YL.顶级需求来源内码,

YL.顶级需求来源分录内码,

YL.父级需求单据内码,

YL.父级需求类型,

YL.父级需求来源单号,

YL.父级需求来源分录号,

YL.物料编码,

YL.物料名称,

YL.固定提前期,

YL.供给单据内码,

YL.供给单编号,

YL.供给单据分录号,

YL.供给类型,

CASE

WHEN YL.供给类型 = '采购订单' THEN CGDD.交货日期

WHEN YL.供给类型 = '采购申请单' THEN CGSQD.到货日期

WHEN YL.供给类型 = '计划订单' THEN  JHDD.需求日期

WHEN YL.供给类型 = '生产订单' THEN  SCDD.生产订单计划完工日期

WHEN YL.供给类型 = '委外订单' THEN  WWDD.委外订单计划完工日期

WHEN YL.供给类型 = '组织间需求单' THEN  ZZXQ.需求日期

WHEN YL.供给类型 = '即时库存' THEN  CONVERT(DATE, GETDATE())

END 业务日期

FROM

(

SELECT

TPR.FBASEDEMANDQTY 业务数量,

TPRE.FBASEQTY 预留数量,

TPR.FSRCINTERID 顶级需求来源内码,

TPR.FSRCENTRYID 顶级需求来源分录内码,

TPR.FPARENTINTERID 父级需求单据内码,

TMOL1.FNAME 父级需求类型,

TPR.FPARENTBILLNO 父级需求来源单号,

TPR.FPARENTENTRYID 父级需求来源分录号, 

TBM.FNUMBER 物料编码,

TBML.FNAME 物料名称,

TBMP.FFIXLEADTIME 固定提前期,

TPRE.FSUPPLYINTERID 供给单据内码,

TPRE.FSUPPLYBILLNO 供给单编号,

TPRE.FSUPPLYENTRYID 供给单据分录号,

TMOL.FNAME 供给类型

FROM

T_PLN_RESERVELINK TPR --预留表头

LEFT JOIN T_BD_MATERIAL TBM ON TPR.FMATERIALID = TBM.FMATERIALID --物料

LEFT JOIN T_BD_MATERIAL_L TBML ON TBM.FMATERIALID = TBML.FMATERIALID AND TBML.FLOCALEID = '2052' --物料多语言

LEFT JOIN T_BD_MATERIALPLAN TBMP ON TBM.FMATERIALID = TBMP.FMATERIALID --物料计划页签

LEFT JOIN T_PLN_RESERVELINKENTRY TPRE ON TPR.FID = TPRE.FID --预留明细

LEFT JOIN T_META_OBJECTTYPE TMO ON TPRE.FSUPPLYFORMID = TMO.FID --业务对象

LEFT JOIN T_META_OBJECTTYPE_L TMOL ON TMO.FID = TMOL.FID AND TMOL.FLOCALEID = '2052' --业务对象多语言

LEFT JOIN T_META_OBJECTTYPE TMO1 ON TPR.FPARENTFORMID = TMO1.FID --业务对象

LEFT JOIN T_META_OBJECTTYPE_L TMOL1 ON TMO1.FID = TMOL1.FID AND TMOL1.FLOCALEID = '2052' --业务对象多语言

LEFT JOIN T_META_OBJECTTYPE TMO2 ON TPR.FDEMANDFORMID = TMO2.FID --业务对象

LEFT JOIN T_META_OBJECTTYPE_L TMOL2 ON TMO1.FID = TMOL2.FID AND TMOL2.FLOCALEID = '2052' --业务对象多语言

--WHERE TPR.FSRCBILLNO = '101XSDD001013'

--WHERE TPR.FSRCBILLNO = '101XSDD009661' OR TPR.FSRCBILLNO = '101XSDD001013'

)YL

--采购订单

LEFT JOIN 

(

SELECT

CAST(TPP.FID AS VARCHAR(50)) FID,

CAST(TPPE.FENTRYID AS VARCHAR(50)) FENTRYID,

CAST(TPPD.FDELIVERYDATE AS DATE) 交货日期

FROM

T_PUR_POORDER TPP

LEFT JOIN T_PUR_POORDERENTRY TPPE ON TPP.FID = TPPE.FID 

LEFT JOIN T_PUR_POORDERENTRY_D TPPD ON TPPE.FENTRYID = TPPD.FENTRYID

)CGDD ON YL.供给单据内码 = CGDD.FID

AND YL.供给单据分录号= CGDD.FENTRYID

--采购申请单

LEFT JOIN (

SELECT

CAST(TPS.FID AS VARCHAR(50)) FID,

CAST(TPSE.FENTRYID AS VARCHAR(50)) FENTRYID,

CAST(TPSE.FARRIVALDATE AS DATE) 到货日期

FROM

T_PUR_REQUISITION TPS 

LEFT JOIN T_PUR_REQENTRY TPSE ON TPS.FID = TPSE.FID

)CGSQD ON YL.供给单据内码= CGSQD.FID

AND YL.供给单据分录号 = CGSQD.FENTRYID

--计划订单

LEFT JOIN (

SELECT

CAST(TPLA.FID AS VARCHAR(50)) FID,

TBM.FNUMBER,

CAST(TPLA.FDEMANDDATE AS DATE) 需求日期

FROM

T_PLN_PLANORDER TPLA

LEFT JOIN T_BD_MATERIAL TBM ON TPLA.FMATERIALID = TBM.FMATERIALID

)JHDD ON YL.供给单据内码 = JHDD.FID

AND YL.物料编码 = JHDD.FNUMBER

--生产订单

LEFT JOIN (

SELECT

CAST(TPM.FID AS VARCHAR(50)) FID,

CAST(TPME.FENTRYID AS VARCHAR(50)) FENTRYID,

CAST(TPME.FPLANFINISHDATE AS DATE) 生产订单计划完工日期

FROM

T_PRD_MO TPM

LEFT JOIN T_PRD_MOENTRY TPME ON TPM.FID = TPME.FID 

)SCDD ON YL.供给单据内码 = SCDD.FID

AND YL.供给单据分录号= SCDD.FENTRYID

--委外订单

LEFT JOIN 

(

SELECT

CAST(TSR.FID AS VARCHAR(50)) FID,

CAST(TSRE.FENTRYID AS VARCHAR(50)) FENTRYID,

CAST(TSRE.FPLANFINISHDATE AS DATE) 委外订单计划完工日期

FROM

T_SUB_REQORDER TSR 

LEFT JOIN T_SUB_REQORDERENTRY TSRE ON TSR.FID = TSRE.FID 

)WWDD ON YL.供给单据内码 = WWDD.FID

AND YL.供给单据分录号 = WWDD.FENTRYID

--组织间需求单

LEFT JOIN (

SELECT

CAST(TPRMT.FID AS VARCHAR(50)) FID,

TBM.FNUMBER,

CAST(TPRMT.FDEMANDDATE AS DATE) 需求日期

FROM

T_PLN_REQUIREMENTORDER TPRMT

LEFT JOIN T_BD_MATERIAL TBM ON TPRMT.FMATERIALID = TBM.FMATERIALID

)ZZXQ ON YL.供给单据内码 = ZZXQ.FID

AND YL.物料编码 = ZZXQ.FNUMBER

)

SELECT

CASE 

WHEN YLCX.父级需求来源单号 = '' THEN 1

ELSE 2

END

AS 层级,

YLCX.预留数量,

YLCX.业务数量,

YLCX.顶级需求来源内码,

YLCX.顶级需求来源分录内码,

YLCX.物料编码,

YLCX.物料名称,

YLCX.固定提前期,

YLCX.父级需求单据内码,

YLCX.父级需求类型,

YLCX.父级需求来源单号,

YLCX.父级需求来源分录号,

YLCX.供给单据内码,

YLCX.供给单据分录号,

YLCX.供给单编号,

YLCX.供给类型,

YLCX.业务日期

FROM YLCX