BOM和工艺路线查询

–多级BOM
SELECT bom.bill_sequence_id,
connect_by_root bb.segment1 bom_item,–主BOM层
bb.segment1 bom_item,–每次重新展开的bom
bim.operation_seq_num,
cc.segment1 component_item,
mp.organization_code || ‘>’ || connect_by_root bb.segment1 || sys_connect_by_path(cc.segment1, ‘>’) code_chain,
bom.organization_id || ‘>’ || connect_by_root bom.bill_sequence_id || sys_connect_by_path(bim.component_sequence_id, ‘>’) id_chain,
lpad(to_char(LEVEL),
decode(LEVEL,
1,
1,
LEVEL + 1),
‘.’) bom_level

FROM bom_bill_of_materials bom,
(SELECT bimm.*
FROM bom_inventory_components bimm
WHERE SYSDATE BETWEEN bimm.effectivity_date AND
nvl(bimm.disable_date,
SYSDATE)) bim,
mtl_system_items bb,
mtl_system_items cc,
mtl_parameters mp
WHERE bom.organization_id = bb.organization_id
AND bom.assembly_item_id = bb.inventory_item_id
AND bom.bill_sequence_id = bim.bill_sequence_id
AND bim.component_item_id = cc.inventory_item_id
AND bom.organization_id = cc.organization_id
AND bom.organization_id = mp.organization_id
AND bom.organization_id = 1188
AND SYSDATE BETWEEN bim.effectivity_date AND
nvl(bim.disable_date,
SYSDATE)
–以上，对递归查询出来的记录做筛选
START WITH bb.segment1 = bb.segment1
AND bb.segment1 = ‘0HA000017C’ --递归开始条件
CONNECT BY PRIOR bim.component_item_id = bom.assembly_item_id --递归条件
ORDER BY lpad(to_char(LEVEL),
decode(LEVEL,
1,
1,
LEVEL + 1),
‘.’) DESC,
bb.segment1,
bim.operation_seq_num;

–单BOM
SELECT bom.ORGANIZATION_ID,
bom.ASSEMBLY_ITEM_ID,
msi.SEGMENT1 “物料” ,
msi.DESCRIPTION “说明”,
msi.PRIMARY_UOM_CODE “单位”,
bom.ALTERNATE_BOM_DESIGNATOR “替代项”,
SYSDATE “日期”, --bom头日期为当前时间
–关联工序
bic.ITEM_NUM “物料序号” ,
bic.OPERATION_SEQ_NUM “工序”,
bic.COMPONENT_ITEM_ID,
msi_c.SEGMENT1 “组件”,
msi_c.DESCRIPTION “物料说明”,
msi_c.PRIMARY_UOM_CODE “组件单位”,
nvl(bic.BASIS_TYPE,1) BASIS_TYPE,
DECODE(nvl(bic.BASIS_TYPE,1),
1,
‘物料’,
2,
‘批次’) “基准”,
bic.COMPONENT_QUANTITY “数量”,
bic.EFFECTIVITY_DATE “有效日期从”,
bic.DISABLE_DATE “有效日期至”,
bic.COMPONENT_YIELD_FACTOR “产出率”,
fcl.meaning “组件类型”,
msi_c.INVENTORY_ITEM_STATUS_CODE “组件状态”,
ml.meaning “供应类型”,
bic.SUPPLY_SUBINVENTORY “子库存”,
msi_c.shippable_item_flag “可发运”,
–关联替代组件
bsc.SUBSTITUTE_COMPONENT_ID,
msi_s.SEGMENT1 “替代组件”,
msi_s.DESCRIPTION “替代组件说明”,
msi_s.PRIMARY_UOM_CODE “替代组件单位”,
bsc.SUBSTITUTE_ITEM_QUANTITY “替代组件数量”

from bom_bill_of_materials bom,
mtl_system_items_b msi,
–关联工序
bom_inventory_components bic,
mtl_system_items_b msi_c,
fnd_common_lookups fcl,
mfg_lookups ml,
–关联替代组件
bom_substitute_components bsc,
mtl_system_items_b msi_s

WHERE 1=1
AND bom.ALTERNATE_BOM_DESIGNATOR IS NULL --主bom
AND bom.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID
AND bom.ORGANIZATION_ID = msi.ORGANIZATION_ID
–关联工序
AND bic.bill_sequence_id = bom.bill_sequence_id
AND bic.COMPONENT_ITEM_ID = msi_c.INVENTORY_ITEM_ID
AND bom.ORGANIZATION_ID = msi_c.ORGANIZATION_ID
AND fcl.lookup_type(+) = ‘ITEM_TYPE’
AND fcl.lookup_code(+) = msi_c.item_type
AND fcl.application_id(+) = 401
AND ml.lookup_code(+) = bic.wip_supply_type
AND ml.lookup_type(+) = ‘WIP_SUPPLY’
–关联替代组件
AND bic.COMPONENT_SEQUENCE_ID = bsc.COMPONENT_SEQUENCE_ID(+)
AND bsc.SUBSTITUTE_COMPONENT_ID = msi_s.INVENTORY_ITEM_ID(+)
AND bom.ORGANIZATION_ID = msi_s.ORGANIZATION_ID(+)

AND bom.ORGANIZATION_ID = 1188
AND bom.ASSEMBLY_ITEM_ID = 2330990
ORDER BY bic.ITEM_NUM ;

–工艺路线
SELECT bor.organization_id,
bor.assembly_item_id,
msi.segment1 “物料”,
msi.description “描述”,
msi.primary_uom_code “单位”,
SYSDATE “日期”, --日期为当前时间
–关联工序
bos.operation_seq_num “工序序号”,
dept.department_code “部门”,
bos.effectivity_date “有效日期”,
bos.disable_date “失效日期”,
bos.operation_description “说明”,
–关联工序资源
brs.resource_seq_num “序号”,
brs.resource_id,
br.resource_code “资源”,
br.UNIT_OF_MEASURE “单位”,
nvl(brs.BASIS_TYPE,1)BASIS_TYPE,
DECODE(nvl(brs.BASIS_TYPE,1),
1,
‘物料’,
2,
‘批次’) “基准”,
brs.USAGE_RATE_OR_AMOUNT “单位使用量”,
brs.ASSIGNED_UNITS “分配数量”,
brs.AUTOCHARGE_TYPE,
DECODE(brs.AUTOCHARGE_TYPE,
1,
‘WIP移动’,
2,
‘人工’,
3,
‘PO接收’,
4,
‘PO移动’) “计费类型”

FROM bom_operational_routings bor,
mtl_system_items_b msi,
–关联工序
bom_operation_sequences bos,
bom_departments dept,
–关联工序资源
bom_operation_resources brs,
bom_resources br

WHERE 1 = 1
AND bor.alternate_routing_designator IS NULL --主工艺路线
AND bor.assembly_item_id = msi.inventory_item_id
AND bor.organization_id = msi.organization_id
–关联工序
AND bor.routing_sequence_id = bos.routing_sequence_id
AND bos.department_id = dept.department_id
–关联工序资源
AND bos.operation_sequence_id = brs.operation_sequence_id(+)
AND brs.resource_id = br.resource_id(+)
AND bor.organization_id = 1188
AND bor.assembly_item_id = 2330990
ORDER BY bos.operation_seq_num,
brs.resource_seq_num;