按物料编码联查文档库资料逻辑梳理
表 | 关键字 | ||
T_PLM_PDM_BASE | 物料库 | Fid(主键) | |
T_PLM_PDM_BASE_m | 物料库_m | fid(主键) | 关联T_PLM_PDM_BASE字段fid |
FERPMATERIALID -记录ERP物料内码 | |||
t_bd_material | 物料 | FMATERIALID(主键) | 关联T_PLM_PDM_BASE_m字段FERPMATERIALID |
T_PLM_CFG_RELATEDOBJECT | 相关对象 | FID(主键) | 关联T_PLM_PDM_BASE(物料库)字段fid |
FRELATEDOBJECT-记录关联文档库内码 | 关联T_PLM_PDM_BASE(文档库)字段fid | ||
T_PLM_PDM_BASE | 文档库 | Fid(主键) | |
T_PLM_PDM_BASE_d | 文档库_d | Fid(主键) | 关联T_PLM_PDM_BASE(文档库)字段fid |
FFILEID-记录文档内码 | 关联T_PLM_PDM_PHYSICALFILE字段FFILEID | ||
T_PLM_PDM_PHYSICALFILE | 文档 | FFILEID(主键)-文档内码 | |
Ffilepath-记录物理路径 |
select T_PLM_CFG_RELATEDOBJECT.FRELATEDOBJECT,T_PLM_CFG_RELATEDOBJECT.* ,T_PLM_PDM_PHYSICALFILE.*
from T_PLM_PDM_BASE --物料库
left join T_PLM_PDM_BASE_m on T_PLM_PDM_BASE.fid = T_PLM_PDM_BASE_m.fid --物料库对应物料ID
left join t_bd_material on T_PLM_PDM_BASE_m.FERPMATERIALID =t_bd_material.FMATERIALID-- 物料表
left join T_PLM_CFG_RELATEDOBJECT on T_PLM_CFG_RELATEDOBJECT.FID = T_PLM_PDM_BASE.FID --相关对象 --字段FRELATEDOBJECT记录文档库内码
LEFT JOIN T_PLM_PDM_BASE WD ON WD.FID = T_PLM_CFG_RELATEDOBJECT.FRELATEDOBJECT --文档库
left join T_PLM_PDM_BASE_d on wd.fid = T_PLM_PDM_BASE_d.fid --文档库对就文件名称
left join T_PLM_PDM_PHYSICALFILE on T_PLM_PDM_BASE_d.FFILEID =T_PLM_PDM_PHYSICALFILE.FFILEID --按文件名称查询物理路径
where t_bd_material.FNUMBER='P11C41PM02126PJ'
--'P99548P109526PJ'
--物料库业务类别:CK4151
推荐阅读