EBS_BOM展开SQL语句
来源:互联网 发布:上海网络综合布线工程 编辑:程序博客网 时间:2024/06/04 01:10
SELECT distinct
bb.BOM层次,
bb.装配件,
bb.装配件说明,
bb.组件序号,
bb.组件,
bb.组件说明,
bb.组件用量,
bb.单位,
bb.利用率,
bb.冲减库房,
bb.冲减货位,
bb.属性,
bb.BOM创建时间,
BOR.COMPLETION_SUBINVENTORY 入库,
MSI.WIP_SUPPLY_SUBINVENTORY 冲减,
MSI.MAXIMUM_ORDER_QUANTITY 最大批量,
MSI.LEAD_TIME_LOT_SIZE 提前期,
MSI.FULL_LEAD_TIME 制造周期,
MSI.FIXED_LOT_MULTIPLIER 固定增加,
MSI.MINIMUM_ORDER_QUANTITY 最小批量,
MSI.FIXED_ORDER_QUANTITY 固定定货量,
MSI.FIXED_DAYS_SUPPLY 固定天数,
MSI.PLANNER_CODE 计划员
FROM MTL_SYSTEM_ITEMS MSI,
BOM_OPERATIONAL_ROUTINGS BOR,
BOM_OPERATION_SEQUENCES BOS,
(select distinct
aa.lvl BOM层次,
msi.segment1 装配件,
msi.description 装配件说明,
aa.item_num 组件序号,
msi1.segment1 组件,
msi1.description 组件说明,
aa.component_quantity 组件用量,
msi1.primary_unit_of_measure 单位,
aa.COMPONENT_YIELD_FACTOR 利用率,
msi1.wip_supply_subinventory 冲减库房,
mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||
mil.segment4 冲减货位,
decode(msi1.planning_make_buy_code,1,'制造',2,'采购') 属性,
to_char(msi1.creation_date,'yyyy.mm.dd hh24:mm.ss') BOM创建时间,
msi1.inventory_item_id
from mtl_system_items_b msi,
mtl_system_items_b msi1,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_item_locations mil,
(
select level lvl,
bic.bill_sequence_id,
bic.component_item_id,
bic.component_quantity,
bic.OPERATION_SEQ_NUM,
bic.COMPONENT_YIELD_FACTOR,
bic.COMPONENT_SEQUENCE_ID,
bic.item_num,
bic.wip_supply_type,
bic.supply_subinventory,
bic.effectivity_date
FROM bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in
(select bill_sequence_id
from bom_bill_of_materials bom2,
inv.mtl_system_items_b msi
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
---------------------------------------输入要查询的项目---------------------------------------------
and msi.segment1 ='ENP00001678'
---------------------------------------输入要查询的项目---------------------------------------------
and msi.organization_id = 137
and bom2.alternate_bom_designator is null)
CONNECT BY bic.bill_sequence_id in prior
(SELECT distinct bill_sequence_id
FROM bom_bill_of_materials BO, inv.mtl_system_items_b msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = 137
and bo.ORGANIZATION_ID = msi.ORGANIZATION_ID
and bo.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID
and bo.alternate_bom_designator is null
and disable_date IS NULL)) aa
where msi.organization_id=137
and msi1.organization_id=137
and bom.organization_id=137
and msi.inventory_item_id=bom.assembly_item_id
and bom.bill_sequence_id=bic.bill_sequence_id
and bic.component_item_id=msi1.inventory_item_id
and bic.disable_date is null
and aa.bill_sequence_id=bic.bill_sequence_id
and aa.component_item_id=msi1.inventory_item_id
and mil.inventory_location_id(+)=msi1.wip_supply_locator_id
and mil.organization_id(+)=137
order by
aa.lvl,
aa.item_num) bb
WHERE BOR.ASSEMBLY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
AND BOS.ROUTING_SEQUENCE_ID(+) = BOR.ROUTING_SEQUENCE_ID
AND MSI.ORGANIZATION_ID(+) = 137
and bor.alternate_routing_designator is null
and bb.inventory_item_id=msi.inventory_item_id
--------------------------------
and msi.segment1 not like '10%'
and msi.segment1 not like '11%'
and msi.segment1 not like '%-MP'
and msi.inventory_item_status_code = 'Active'
------------------------------
order by
bb.BOM层次,
bb.装配件,
bb.装配件说明,
bb.组件序号,
bb.组件
bb.BOM层次,
bb.装配件,
bb.装配件说明,
bb.组件序号,
bb.组件,
bb.组件说明,
bb.组件用量,
bb.单位,
bb.利用率,
bb.冲减库房,
bb.冲减货位,
bb.属性,
bb.BOM创建时间,
BOR.COMPLETION_SUBINVENTORY 入库,
MSI.WIP_SUPPLY_SUBINVENTORY 冲减,
MSI.MAXIMUM_ORDER_QUANTITY 最大批量,
MSI.LEAD_TIME_LOT_SIZE 提前期,
MSI.FULL_LEAD_TIME 制造周期,
MSI.FIXED_LOT_MULTIPLIER 固定增加,
MSI.MINIMUM_ORDER_QUANTITY 最小批量,
MSI.FIXED_ORDER_QUANTITY 固定定货量,
MSI.FIXED_DAYS_SUPPLY 固定天数,
MSI.PLANNER_CODE 计划员
FROM MTL_SYSTEM_ITEMS MSI,
BOM_OPERATIONAL_ROUTINGS BOR,
BOM_OPERATION_SEQUENCES BOS,
(select distinct
aa.lvl BOM层次,
msi.segment1 装配件,
msi.description 装配件说明,
aa.item_num 组件序号,
msi1.segment1 组件,
msi1.description 组件说明,
aa.component_quantity 组件用量,
msi1.primary_unit_of_measure 单位,
aa.COMPONENT_YIELD_FACTOR 利用率,
msi1.wip_supply_subinventory 冲减库房,
mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||
mil.segment4 冲减货位,
decode(msi1.planning_make_buy_code,1,'制造',2,'采购') 属性,
to_char(msi1.creation_date,'yyyy.mm.dd hh24:mm.ss') BOM创建时间,
msi1.inventory_item_id
from mtl_system_items_b msi,
mtl_system_items_b msi1,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_item_locations mil,
(
select level lvl,
bic.bill_sequence_id,
bic.component_item_id,
bic.component_quantity,
bic.OPERATION_SEQ_NUM,
bic.COMPONENT_YIELD_FACTOR,
bic.COMPONENT_SEQUENCE_ID,
bic.item_num,
bic.wip_supply_type,
bic.supply_subinventory,
bic.effectivity_date
FROM bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in
(select bill_sequence_id
from bom_bill_of_materials bom2,
inv.mtl_system_items_b msi
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
---------------------------------------输入要查询的项目---------------------------------------------
and msi.segment1 ='ENP00001678'
---------------------------------------输入要查询的项目---------------------------------------------
and msi.organization_id = 137
and bom2.alternate_bom_designator is null)
CONNECT BY bic.bill_sequence_id in prior
(SELECT distinct bill_sequence_id
FROM bom_bill_of_materials BO, inv.mtl_system_items_b msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = 137
and bo.ORGANIZATION_ID = msi.ORGANIZATION_ID
and bo.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID
and bo.alternate_bom_designator is null
and disable_date IS NULL)) aa
where msi.organization_id=137
and msi1.organization_id=137
and bom.organization_id=137
and msi.inventory_item_id=bom.assembly_item_id
and bom.bill_sequence_id=bic.bill_sequence_id
and bic.component_item_id=msi1.inventory_item_id
and bic.disable_date is null
and aa.bill_sequence_id=bic.bill_sequence_id
and aa.component_item_id=msi1.inventory_item_id
and mil.inventory_location_id(+)=msi1.wip_supply_locator_id
and mil.organization_id(+)=137
order by
aa.lvl,
aa.item_num) bb
WHERE BOR.ASSEMBLY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
AND BOS.ROUTING_SEQUENCE_ID(+) = BOR.ROUTING_SEQUENCE_ID
AND MSI.ORGANIZATION_ID(+) = 137
and bor.alternate_routing_designator is null
and bb.inventory_item_id=msi.inventory_item_id
--------------------------------
and msi.segment1 not like '10%'
and msi.segment1 not like '11%'
and msi.segment1 not like '%-MP'
and msi.inventory_item_status_code = 'Active'
------------------------------
order by
bb.BOM层次,
bb.装配件,
bb.装配件说明,
bb.组件序号,
bb.组件
阅读全文
0 0
- EBS_BOM展开SQL语句
- BOM展开SQL语句
- 求BOM根据阶数展开的SQL语句
- 【SQL优化】子查询展开
- SQL语句
- sql语句
- SQL语句
- SQL语句
- SQL语句
- SQL语句
- SQL语句
- SQL语句
- SQL 语句
- sql语句
- sql语句
- Sql语句
- SQL语句
- SQL语句
- Raft membership change
- Python学习——爬虫之pdfkit用法
- 坐标系变换理论推导
- 网易笔试题
- Python3中urllib详细使用方法
- EBS_BOM展开SQL语句
- 正则表达式 处理选项
- Weave 如何与外网通信?- 每天5分钟玩转 Docker 容器技术(66)
- 【TP】【P-sensor】TP模拟P-sensor
- sql语句实现的具体过程
- 软件测试实验室之Eclipse
- python Django web初学安装
- eclipse中如何正确打开对应格式的文件比如.ftl , .bpmn等
- 数据结构之栈的应用(1) 用栈模拟Hanoi塔