BOM相关问题

Bom主要用的两张表：
表名: bom.bom_bill_of_materials （BOM清单父项目）
表名：bom.bom_inventory_components （说明：BOM清单构成项目）
示例代码：
–获取BOM ATO层物料属性
BEGIN
SELECT bic.component_item_id
INTO l_component_item_id
FROM bom_inventory_components bic,
bom_bill_of_materials bom,
mtl_system_items_b msi,
CUX_CREATE_OE_ORDER_TEMP caql
WHERE bic.bill_sequence_id = bom.bill_sequence_id
AND bom.alternate_bom_designator IS NULL
AND bic.component_item_id = msi.inventory_item_id
AND bom.organization_id = msi.organization_id
AND nvl(bic.disable_date, SYSDATE + 1) > SYSDATE
AND msi.bom_item_type = 1 – 模型层
AND bom.assembly_item_id = caql.inventory_item_id
AND bom.organization_id = p_organization_id
AND caql.group_id = p_group_id
AND caql.line_number = p_line_number
GROUP BY bic.component_item_id, msi.segment1;
表名: bom.bom_bill_of_materials
说明: BOM清单父项目
BILL_SEQUENCE_ID NUMBER 清单序号（关键字）
ASSEMBLY_ITEM_ID NUMBER 装配件内码
ORGANIZATION_ID NUMBER 组织代码
ASSEMBLY_TYPE NUMBER 装配类别
SPECFIIC_ASSEMBLY_COMMENT VARCHAR2(240) 注释（装配件状态P、R等）
COMMON_ORGANIZATION_ID NUMBER 公共组织
COMMON_BILL_SEQUENCE_ID NUMBER 公共序号
COMMON_ASSEMBLY_ITEM_ID NUMBER 公共项目内码

表名：bom.bom_inventory_components
说明：BOM清单构成项目
COMPONENT_SEQUENCE_ID NUMBER 构件序号
BILL_SEQUENCE_ID NUMBER 清单序号
OPERATION_SEQ_NUM NUMBER 操作序列号
COMPONENT_ITEM_ID NUMBER
ITEM_NUM NUMBER 项目序列号
COMPONENT_QUANTITY NUMBER 构件数量
COMPONENT_YIELD_FACTOR NUMBER 产出因子
EFFECTIVITY_DATE DATE 生效日期
DISABLE_DATE DATE 失效日期
CHANGE_NOTICE VARCHAR2(10) 更改注释(ECO更改号)
PLANNING_FACTOR NUMBER 计划百分比
QUANTITY_RELATED NUMBER 相关数量
SO_BASIS NUMBER
OPTIONAL NUMBER 是否可选(1.是;2.否)
MUTUALLY_EXCLUSIVE_OPTIONS NUMBER 互斥标识(1.是;2.否)
WIP_SUPPLY_TYPE NUMBER 车间供应类型(1.推式2.装配拉式3.操作拉式
4.大量5.供应商6.虚拟)
supply_subinventory 供应子库存
supply_locator_id 供应货位ID
BOM_ITEM_TYPE NUMBER 清单项目类型(1.模型2.选项类3.计划中4.标准)
INCLUDE_ON_SHIP_DOCS NUMBER 包含在发运文档中(1.是;2.否)
REQUIRED_FOR_REVENUE NUMBER 需要进款(1.是;2.否)
REQUIRED_TO_SHIP NUMBER 需要发运(1.是;2.否)
SHIPPING ALLOWED NUMBER 搬运允许(1.是;2.否)
INCLUDE_IN_COST_ROLLUP NUMBER 计入成本累加中(1.是;2.否)
CHECK_ATP NUMBER ATP检查(1.是;2.否)
根据已有的物料，可以向下或者是向上展开bom，主表就是物料清单父表和物料清单表，方法是递归

SELECT organization_id,
assembly_item_id,
(select segment1 from mtl_system_items_b msib
where msib.inventory_item_id= t.assembly_item_id
andmsib.organization_id=86 ) assembly_segment1,
(select segment1 from mtl_system_items_b msib
where msib.inventory_item_id= t.component_item_id
andmsib.organization_id=86 ) component_segment1,
component_item_id,
tree_level,
tree_max_level,
tree_node,
max_tree_node,
bill_sequence_id,
component_sequence_id,
first_bill_sequence_id,
parent_bill_sequence_id,
parent_item_id,
(select segment1 from mtl_system_items_b msib
where msib.inventory_item_id= t.parent_item_id
andmsib.organization_id=86 ) parent_segment1,
last_update_days,
ITEM_NUM
FROM (SELECTbom.organization_id,
bom.ASSEMBLY_ITEM_ID,–c.assembly_item_id AS assembly_item_id,
bic.component_item_id,
LEVEL AS tree_level,
MAX(LEVEL) over(PARTITION BYbom.organization_id) AS tree_max_level,
row_number() over(PARTITION BYbom.organization_idORDER BY 1) AS tree_node,
COUNT(1) over(PARTITION BYbom.organization_id) AS max_tree_node,
bom.bill_sequence_id,
bic.component_sequence_id,
first_value(bom.bill_sequence_id) over(PARTITION BYbom.organization_id) AS first_bill_sequence_id,
PRIOR bic.bill_sequence_id AS parent_bill_sequence_id,
PRIORbic.component_item_idAS parent_item_id,
MIN((SYSDATE - bic.last_update_date)) over(PARTITION BYbom.organization_id) AS last_update_days,
bic.ITEM_NUM
FROM bom_inventory_components bic, bom_bill_of_materials bom
WHERE bic.bill_sequence_id = bom.bill_sequence_id
AND bom.alternate_bom_designator IS NULL
AND bic.DISABLE_DATE IS NULL
START WITH bom.assembly_item_id = 35489–c.assembly_item_id
ANDbom.organization_id= 86 --目前只取86的数据
CONNECT BY PRIORbic.component_item_id= bom.assembly_item_id
AND PRIORbom.organization_id=bom.organization_id
) t
WHERE (t.last_update_days <= null OR null IS NULL)
order by tree_level,ITEM_NUM,assembly_segment1;