14.移转 Aras PLM大小事-开发报表 Where Used Top Level Product

本篇放上获得最上阶产品料号的作法
1.建立报表
2.设定SQL语法
3.设定Method
4.设定XSLT
参考程序码
https://github.com/panda0909/Aras-PLM-Exercise/tree/master/Report/Where%20Used%20Top%20Level%20Product

1.建立报表

2.设定SQL语法-用来查询最上阶层料号

---递回查询
with [bom_new] as (
select pbom.SOURCE_ID as tr_sid,pbom.RELATED_ID as tr_rid,p.ITEM_NUMBER as parent,pr.ITEM_NUMBER as son ,pr.NAME,1 as level from [innovator].[PART_BOM] as pbom
inner join [innovator].[part] as p on pbom.SOURCE_ID=p.id
inner join [innovator].[part] as pr on pbom.RELATED_ID=pr.id
where pr.id=@PART_ID and p.IS_CURRENT = '1'
---想要查询的零件料号

---联集所有上层料号
UNION ALL
select pbom2.SOURCE_ID as tr_sid,pbom2.RELATED_ID as tr_rid,p.ITEM_NUMBER as parent,pr.ITEM_NUMBER as son,pr.NAME,level + 1
from [innovator].[PART_BOM] as pbom2
inner join [bom_new] as bnew on bnew.tr_sid=pbom2.RELATED_ID
inner join [innovator].part as p on pbom2.SOURCE_ID =p.id
inner join [innovator].[part] as pr on pbom2.RELATED_ID=pr.id
where p.IS_CURRENT='1'
)
---料号中开头为P成品件
select ta.parent as item_number from [bom_new] as ta where ta.parent like 'P%'

3.设定Method-透用Method呼叫查询SQL

private Item GetAllItems(string part_id)
{
    Item callframe = inn.newItem("SQL", "SQL PROCESS");
    callframe.setProperty("name", "JPC_WhereUsedTopLevelBOM");
    callframe.setProperty("PROCESS", "CALL");
    callframe.setProperty("ARG1", part_id);
    
    return callframe.apply();
}