直接用SQL写出带扩展数量的BOM多级清单

直接用SQL写出带扩展数量的BOM多级清单。

某项目需要，不能写函数，必须直接用SQL，性能差点没关系。

客户BOM有副产品，也有为0的情况，也就是组件数量有正、负、0，所以情况稍微麻烦了点
只用SQL的原因可能为：
因为没有权限去修改和定义程序。

我们唯一能用的手段就是Discover Report，因此需要用SQL来实现多级BOM展开。

with t as(
SELECT anization_id,
anization_id || '>' || connect_by_root assembly_number ||
sys_connect_by_path(ponent_number, '>') code_chain,
anization_id || '>' || connect_by_root bill_sequence_id ||
sys_connect_by_path(ponent_sequence_id, '>') id_chain,
connect_by_root assembly_number assembly_number,
boms.assembly_description,
LEVEL bom_level,
ponent_number component_number,
lpad(' ', (LEVEL - 1) * 2, ' ') || ponent_number
ind_component_number,
ponent_description,
boms.primary_uom_code uom,
ponent_quantity component_quantity,
boms.planning_factor,
ponent_yield_factor,
boms.effectivity_date
FROM (SELECT anization_id,
bom1.assembly_item_id,
mst1.segment1 assembly_number,
mst1.description assembly_description,
bom1.bill_sequence_id bill_sequence_id,
bom1.alternate_bom_designator assembly_alternate,
ponent_sequence_id,
ponent_item_id,
mstc.segment1 component_number,
mstc.description component_description,
mstc.primary_uom_code,
ponent_quantity,
bomc.effectivity_date,
bomc.planning_factor,
ponent_yield_factor,
bomc.supply_subinventory
FROM apps.bom_bill_of_materials bom1,
inv.mtl_system_items_b mst1,
apps.bom_inventory_components bomc,
inv.mtl_system_items_b mstc
WHERE anization_id = anization_id
AND bom1.assembly_item_id = mst1.inventory_item_id
AND bom1.bill_sequence_id = bomc.bill_sequence_id
AND anization_id = anization_id
AND ponent_item_id = mstc.inventory_item_id
--Item
AND mst1.bom_enabled_flag = 'Y'
AND mst1.bom_item_type IN (1, 2, 3, 4) --Dependent
--BOM Header
AND bom1.assembly_type = 1 --1 Manufature,2 ENG
AND nvl(bom1.effectivity_control, 1) <= 3
--BOM Line
AND nvl(bomc.disable_date, SYSDATE) >= SYSDATE
AND bomc.effectivity_date <= SYSDATE
AND bomc.implementation_date IS NOT NULL
AND nvl(bomc.eco_for_production, 2) = 2
--Filters
AND anization_id = 207
AND bom1.alternate_bom_designator IS NULL) boms CONNECT BY PRIOR anization_id = anization_id AND PRIOR ponent_item_id = boms.assembly_item_id )
SELECT anization_id,
t1.code_chain,
t1.assembly_number,
t1.assembly_description,
t1.bom_level,
ponent_number,
ponent_description,
t1.uom,
t1.ind_component_number,
t1.id_chain,
ponent_quantity,
(SELECT power(10,
SUM(CASE
WHEN ponent_quantity = 0 THEN
ELSE
log(10, abs(ponent_quantity))
END)) *
decode(MOD(COUNT(decode(sign(ponent_quantity), -1, 1)), 2), 1, -1, 1) *
(CASE
WHEN COUNT(decode(ponent_quantity, 0, 1)) >= 1 THEN
ELSE
1
END)
FROM t t2
WHERE t1.id_chain LIKE t2.id_chain || '%') extended_quantity,
t1.planning_factor,
ponent_yield_factor,
t1.effectivity_date
FROM t t1
ORDER BY t1.id_chain;。