Agile PLM数据库表结构(Oracle)
刚进公司，任务是接管PLM系统，但是还在给外包团队开发，没有代码。无妨先看业务和数据库，ok，业务看不懂，只能先看数据库，数据库没有数据字典，这个系统没有任何文档产出......练手时发现数据库类型是Oracle，面对百度不成问题，数据字典只能看前端然后去数据库里面一个个找着对应自己整理了，纯折磨
只能一点点来了，我真的服了，没有数据字典给我，让我熟悉这个系统。可能使用到了一个就来改一下，网上的资料也比较少
文章参考：https://blog.csdn.net/namelessmyth/article/details/124372370
基本上全抄 ，带入理解。公司数据字典里面可以忽略此部分。系统时区是0时区，我们这边是东8得加8个小时t1.CREATE_DATE + INTERVAL '8' HOUR
目录
数据表
物料 ITEM
| 字段名 | 中文名 |
|---|---|
| ITEM_NUMBER | 物料编码 |
| CLASS | 一级分类 |
| SUBCLASS | 二级分类 |
| DESCRIPTION | 描述（物料名称） |
| DEFAULT_CHANGE | 最新的变更单ID |
| RELEASE_TYPE | 生命周期ID |
| REV_NUMBER | 版本号 |
-- 物料表
select * from agile.item;
-- 物料视图
select * from agile.ITEM_query;
select * from agile.ITEM_P2_QUERY;
select * from agile.ITEM_P2P3_QUERY;
select * from agile.ITEM_P2P3_QUERY_ALL_REV;
-- 物料历史记录
select i.item_number,t.*
from ITEM_HISTORY t
inner join ITEM i on t.item = i.id
where I.ITEM_NUMBER like '03.08.%'
BOM
| 字段名 | 中文名 |
|---|---|
| ID | bom表主键 每次变更单对BOM有任务修改都会追加记录，而不是直接修改原纪录 |
| ITEM | 父物料ID |
| COMPONENT | 子物料ID |
| ITEM_NUMBER | 子物料编码 |
| QUANTITY | 子料用量，文本字段 可支持分数，例如：1/6 |
| CHANGE_IN | 子物料被加入的变更单ID 如果为0代表是走变更之前（初始状态）就添加到BOM中的 |
| CHANGE_OUT | 子物料被删除的变更单ID 当这个字段值>0时代表当前这个子行已经被这个变更单作废了 |
| PRIOR_BOM | 如果不为空，代表： 该行数据变更了另一行数据，值为另一行数据ID |
| FLAGS | bom子项状态（目前仅第4，第5位数有效） 第4位：0代表无效，1代表有效 第5位：0代表已过时，1代表最新 |
场景说明：
-
变更单C0095696，状态为：新建，受影响物件为90.01.002271
- 在父料中新增1行子料03.10.000488，则BOM表会新增1行记录，flag=00000，change_in=该变更单ID，change_out=空，prior_bom=空
- 在父料中删除1行子料06.06.034701，则BOM表会新增1行记录，flag=00000，change_in=该变更单ID，change_out=该变更单ID，prior_bom=被删除的数据ID
- 在父料中将1行子料03.15.030311修改为03.15.010001R，则BOM表会新增1行记录，flag=00000，change_in=该变更单ID，change_out=空，prior_bom=被删除的数据ID
- 在父料中将1行子料02.80.603667的用量从6改成5，则BOM表会新增1行记录，flag=00000，change_in=该变更单ID，change_out=空，prior_bom=被修改的数据ID
-
变更单C0095696，状态变为：审核，受影响物件为90.01.002271
- 以上数据均无任何变化
-
变更单C0095696，状态变为：发放，受影响物件为90.01.002271
- 在父料中新增1行子料03.10.000488，则BOM表会新增1行记录，flag变为00011，change_in为该变更单ID，change_out为空，prior_bom为空
- 在父料中删除1行子料06.06.034701，则BOM表会新增1行记录，flag依然为00000，change_in为该变更单ID，change_out为该变更单ID，prior_bom指向被删除的数据ID；被删除的数据flag从00011变为00010，change_out变为当前变更单ID
- 在父料中将1行子料03.15.030311修改为03.15.010001R，则BOM表会新增1行记录，flag变为00011，change_in为该变更单ID，change_out为空，prior_bom指向被删除的数据ID；被删除的数据flag从00011变为00010，change_out变为当前变更单ID
- 在父料中将1行子料02.80.603667的用量从6改成5，则BOM表会新增1行记录，flag变为00011，change_in为该变更单ID，change_out为空，prior_bom指向被修改的数据ID；被删除的数据flag从00011变为00010，change_out变为当前变更单ID
with e as (
select c.change_number
,i.item_number as parent_item_number,i.description as parent_item_name
,case when (change_out > 0 and prior_bom > 0) then 2 else 1 end as op_type
,ci.description as item_name
,b.item_number,b.id,b.prior_bom,b.quantity as qty,b.FLAGS,b.created,b.last_upd,b.change_in,b.change_out
,f.text as remark
,decode(b.prior_bom,0,b.id,b.prior_bom) as order_id
from AGILE.Change C
INNER JOIN AGILE.REV R ON R.CHANGE = C.ID
INNER JOIN AGILE.bom b on r.item = b.item and b.change_in = c.id
inner join AGILE.item i on r.item = i.id
inner join AGILE.item ci on b.component = ci.id
left join agile.agile_flex f on b.id = f.row_id and f.class = 10000 and f.attid = 1036
LEFT JOIN AGILE.LISTENTRY LDELAY ON R.LIST01 = LDELAY.ENTRYID AND LDELAY.LANGID = 4
where 1=1
${if(trim(changeNumber) == '',""," and c.change_number = '"+changeNumber+"'")}
${if(trim(pitemNumber) == '',""," and i.item_number = '"+pitemNumber+"'")}
)
select * from (
-- 操作BOM
select e.* from e
union all
-- 被操作的BOM
select e.change_number
,e.parent_item_number,e.parent_item_name
,2 as op_type
,ci.description as item_name
,b.item_number,b.id,b.prior_bom,b.quantity as qty,b.FLAGS,b.created,b.last_upd,b.change_in,b.change_out
,f.text as remark
,decode(b.prior_bom,0,b.id,b.prior_bom) as order_id
from agile.bom b
inner join e on b.id = e.prior_bom
inner join AGILE.item ci on b.component = ci.id
left join agile.agile_flex f on b.id = f.row_id and f.class = 10000 and f.attid = 1036
where e.change_in != e.change_out
) t order by t.parent_item_number,t.change_number,t.order_id
活动 ACTIVITY
业务关系：
计划(项目)包含阶段, 阶段包含任务和关口, 某些任务完成以后, 关口才能打开, 才能进行下一阶段的任务
| 字段名 | 中文名 |
|---|---|
| class | 大类(关口和活动) |
| subclass | 小类(关口, 任务, 计划, 阶段) subclass=18027：计划(项目) subclass=18028：阶段 subclass=18029：任务 subclass=18401：关口 |
| PARENT_ID | 上一层对象的id 比如：任务的parent_id会指向阶段 阶段的parent_id指向计划 |
| ROOT_ID | 最上层对象的id,也就是计划的id |
| status | 状态信息-状态18516 未开始 18517 进程中 18518 完成 18519 已取消 18540 已取消 18539 已打开 18537 已关闭 18538 审阅中 |
价格 PRICE
PRICE：对应价格
PRICELINE：对应价格的定价页签数据。也就是价格里面一行，即阶梯价格。当价格新建之后会有一条很多字段都是null与之对应，往定价中新增记录之后会是2条数据。
价格和BOM类似也存在版本管理,在流程中也能对价格进行增删改操作,流程走到发布节点才会生效.流程中未生效的数据也是存在PRICELINE表的.
| 字段名 | 中文名 |
|---|---|
| price_type | 价格类型 1：代表量产价 0：为样品价 |
| price_number | 价格编号 |
| owner | 创建人 |
| supplier | 对应的供应商 |
| item | 对应的物料 制造商部件型价格这个字段为空。 |
| mfr_part | 对应的制造商部件 物料型价格这个为空。 |
| default_change | 默认的最新的价格变更 |
| delete_flag | 删除标识 0：代表未删除。 |
| flags | 状态标识 刚新建时第三位是1 加入变更单之后第4位变为1 |
| part_number | 物料型价格 这个字段记录物料编码 制造商部件价格这个字段记录： 制造商名称::制造商部件编号 |
-- 价格主表
SELECT * FROM agile.PRICE p;
-- 价格子行
SELECT * FROM agile.priceline pl;
价格子行 PRICELINE
| 字段名 | 中文名 |
|---|---|
| price | 外键，关联price表ID。 |
| change_in | 价格行新增的表更单id 如果是0代表走变更之前就新增好的 如果是空则代表无效数据。 |
| change_out | 只要有值代表该行已被删除且流程已发布 流程如果还未发布，则依然会是0 删除别人的行in和out相等。 |
| prior_row | 如果该行修改了另一个行 这里记录被操作行id。 |
| flags | 状态标识 在变更中新增全为0 变更之前新增第4和第5位为1 不受PCO影响。 |
| quantity | 数量 |
| effective_from_date | 生效开始日期 |
| effective_end_date | 生效结束日期 |
| currency_code | 货币代码 |
| material_price | 材料价格 |
-- 变更 2473549:EcoBOM
select * from agile.change c where c.CHANGE_NUMBER in ('C0095253');
--变更受影响物件查询
SELECT c.change_number, i.*
FROM AGILE.REV R
INNER JOIN AGILE.ITEM I ON R.ITEM = I.ID
INNER JOIN AGILE.CHANGE C ON R.CHANGE = C.ID
INNER JOIN agile.LANGTABLE l on c.SUBCLASS = l.id and l.type = 4450 and l.langid = 4
WHERE I.CLASS = 10000 AND C.CREATE_DATE > '2018-01-01'
and l.value in ('自制件料号申请与BOM发布流程','ECO-BOM变更流程')
变更 流程？CHANGE
| 字段名 | 中文名 |
|---|---|
| CHANGE_NUMBER | 编号 |
| CLASS | 一级分类 |
| SUBCLASS | 二级分类 |
| CREATE_DATE | 创建日期 |
| STATUS | 状态 |
| RELEASE_DATE | 生命周期日期 公司系统这里不为null表示已完成 |
| DELETE_FLAG | 删除标记 为null表示没删除 |
-- 变更 2473549:EcoBOM
select * from agile.change c where c.CHANGE_NUMBER in ('C0095253');
-- 变更受影响物件查询
SELECT c.change_number, i.*
FROM AGILE.REV R
INNER JOIN AGILE.ITEM I ON R.ITEM = I.ID
INNER JOIN AGILE.CHANGE C ON R.CHANGE = C.ID
INNER JOIN agile.LANGTABLE l on c.SUBCLASS = l.id and l.type = 4450 and l.langid = 4
WHERE I.CLASS = 10000 AND C.CREATE_DATE > '2018-01-01'
and l.value in ('自制件料号申请与BOM发布流程','ECO-BOM变更流程')
供应商 ORGANIZATION
--供应商
select * from ORGANIZATION where org_number in ('RM.00652');
制造商 MANUFACTURERS
MANUFACTURERS：制造商
MANU_PARTS：制造商部件
MANU_BY：制造商和部件关系表
--制造商
select * from agile.MANUFACTURERS b where b.name = '昆晶冷片（深圳）电子有限公司';
--制造商部件
select * from agile.MANU_PARTS p where p.PART_NUMBER in ('1N5242B');
--物料和部件关联关系
select * from agile.MANU_BY b where B.MANU_PART != 0;
产品服务请求 PSR
--缺陷
select * from PSR t where t.status = 2477843;
文件 Floder
Floder：文件夹表
Attachment：附件表
历史记录
EVENT_HISTORY：事件历史表
CHANGE_HISTORY：变更历史表
ITEM_HISTORY：物件历史表
公共表
二级分类描述 LANGTABLE
-- 查询CHANGE表中的STATUS和SUBCLASS描述，查询条件：等于LANGTABLE中的ID，取等于LANGTABLE中的VALUE
select t1.CHANGE_NUMBER,t1.CREATE_DATE + INTERVAL '8' HOUR as "time", t2.VALUE as "status", t3.value from AGILE.CHANGE_P2P3 t1
left join AGILE.LANGTABLE t2 on t2.ID = t1.STATUS and t2.TYPE = 4450
left join AGILE.LANGTABLE t3 on t3.ID = t1.SUBCLASS and t3.TYPE = 4450
where t3.VALUE != '完成' and t3.VALUE != '取消' and t1.DELETE_FLAG IS NULL
第二页，第三页 PAGE_TWO, PAGE_THREE
PLM中大部分数据对象都会有第二页和第三页。
第二页中存储的是分类中一二级分类的公共字段。对应的表是PAGE_TWO
第三页中存储的是三级分类中特殊字段。对应的表是PAGE_THREE
PLM中很多常用对象的第二页，第三页信息已经做了视图，可以直接用视图来查询
-- 第二页
select * from agile.PAGE_TWO t2;
-- 第三页
select * from agile.PAGE_THREE t2;
-- 物件关联第二页
select * from item i
inner join page_two p2 on i.id=p2.id and i.class = p2.class
在第二页、第三页中的 大文本字段 和 多列表字段：
大文本字段：对于varchar2 4000以内的内容直接存储在第二页和第三页。但如果超过4000则是存在下面的表中。
-- 大文本存储表,bom注释(id为物料id,row_id为bom的id)
select * from AGILE_FLEX t
inner join bom b on t.row_id = b.id and t.id = b.item
where t.text in ('sjj2','sjj');
多列表字段：多列表选择的值数量比较小时是以",id1,id2,id3,"存储在第二页和第三页中的。但如果超过一定数量这个字段会变成“-1”然后需要从msatt表查询。
拿对应的id去 列表表 listname 和 列表项 Listentry 查询，列表项分类id可以去后台管理系统的“设置-数据设置-列表”中查询
| 字段名 | 中文名 |
|---|---|
| entryid | 列表项id |
| entryvalue | 列表项值 |
| parent_entry | 列表项之间父子关系 |
| parentid | 列表项分类 |
--列表定义表,层叠列表用parent_list链接父子列表
select * from agile.LISTNAME WHERE name like '%物料组%';
--列表子行 父子记录通过parent_entry关联,parentid用于区分不同的列表项
select * from agile.LISTENTRY l where l.parentid in (2477261) and langid in (0,4);
--修复生命周期专用SQL display值应该为0.
select * from agile.listname where id = 301;
update listname set display = 0 where id = 301;
--生命周期查询
select ip.*,lp.value
from agile.ITEM_QUERY ip
INNER JOIN agile.LANGTABLE lp
ON ip.RELEASE_TYPE = lp.id AND lp.type = 4450 AND lp.langid = 4
用户 AGILEUSER
-- plm用户表
select * from agile.AGILEUSER where loginid = 'zhangsan'
-- 用户组
select * from agile.user_group where id = 6039858
货币转换 CONVERSION
-- 转换(货币)
select * from agile.CONVERSION c where c.CONV_EFFECTIVE_TO_DATE is null;
系统配置
-- 查询PLM进程扩展
select * from PROPERTYTABLE p
inner join NODETABLE n on p.parentid = n.id
where n.PARENTID = 10025 and p.value like '%PCBCreateItem%'
-- 查询PLM事件程序
select * from agile.PROPERTYTABLE p
inner join agile.NODETABLE n on p.parentid = n.id and propertyid = 885
where n.PARENTID = 2000011631 and p.value like '%CheckSCSupplier%'
