Oracle EBS-SQL (BOM-17):检查8层BOM.sql
define item1="1234567890"
select
a1.产品编码,
a1.产品描述,
'1层' 层数,
a1.物料编码,
a1.物料描述,
a1.单位,
a1.装配数量,
A1.备注,
'2层' 层数,
a2.物料编码,
a2.物料描述,
a2.单位,
a2.装配数量,
A2.备注,
'3层' 层数,
a3.物料编码,
a3.物料描述,
a3.单位,
a3.装配数量,
A3.备注,
'4层' 层数,
a4.物料编码,
a4.物料描述,
a4.单位,
a4.装配数量,
A4.备注,
'5层' 层数,
a5.物料编码,
a5.物料描述,
a5.单位,
a5.装配数量,
A5.备注,
'6层' 层数,
a6.物料编码,
a6.物料描述,
a6.单位,
a6.装配数量,
A6.备注,
'7层' 层数,
a7.物料编码,
a7.物料描述,
a7.单位,
a7.装配数量,
A7.备注,
'8层' 层数,
a8.物料编码,
a8.物料描述,
a8.单位,
a8.装配数量,
A8.备注
from
(Select
MSI1.Segment1 产品编码,
MSI1.Description 产品描述,
MSI2.Segment1 物料编码,
MSI2.Description 物料描述,
MSI2.PRIMARY_UOM_CODE 单位,
BIC.COMPONENT_QUANTITY 装配数量,
BIC.Last_Update_Date 更改日期,
bic.component_remarks 备注
From
INV.MTL_SYSTEM_ITEMS_B MSI1,
BOM.BOM_BILL_OF_MATERIALS BOM,
BOM.BOM_INVENTORY_COMPONENTS BIC,
INV.MTL_SYSTEM_ITEMS_B MSI2
Where
MSI1.SEGMENT1 like '&item1'
And MSI1.Organization_Id = X
And BOM.ASSEMBLY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
And BOM.Organization_Id = MSI1.Organization_Id
And BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
And ( BIC.DISABLE_DATE IS NULL OR BIC.DISABLE_DATE > sysdate )
And MSI2.INVENTORY_ITEM_ID(+) = BIC.COMPONENT_ITEM_ID
And MSI2.Organization_Id(+) = X
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
)a1,
(Select
MSI1.Segment1 产品编码,
MSI1.Description 产品描述,
MSI2.Segment1 物料编码,
MSI2.Description 物料描述,
MSI2.PRIMARY_UOM_CODE 单位,
BIC.COMPONENT_QUANTITY 装配数量,
BIC.Last_Update_Date 更改日期,
bic.component_remarks 备注
From
INV.MTL_SYSTEM_ITEMS_B MSI1,
BOM.BOM_BILL_OF_MATERIALS BOM,
BOM.BOM_INVENTORY_COMPONENTS BIC,
INV.MTL_SYSTEM_ITEMS_B MSI2
Where
MSI1.Organization_Id = X
And BOM.ASSEMBLY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
And BOM.Organization_Id = MSI1.Organization_Id
And BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
And ( BIC.DISABLE_DATE IS NULL OR BIC.DISABLE_DATE > sysdate )
And MSI2.INVENTORY_ITEM_ID(+) = BIC.COMPONENT_ITEM_ID
And MSI2.Organization_Id(+) = X
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
)a2,
(Select
MSI1.Segment1 产品编码,
MSI1.Description 产品描述,
MSI2.Segment1 物料编码,
MSI2.Description 物料描述,
MSI2.PRIMARY_UOM_CODE 单位,
BIC.COMPONENT_QUANTITY 装配数量,
BIC.Last_Update_Date 更改日期,
bic.component_remarks 备注
From
INV.MTL_SYSTEM_ITEMS_B MSI1,
BOM.BOM_BILL_OF_MATERIALS BOM,
BOM.BOM_INVENTORY_COMPONENTS BIC,
INV.MTL_SYSTEM_ITEMS_B MSI2
Where
MSI1.Organization_Id = X
And BOM.ASSEMBLY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
And BOM.Organization_Id = MSI1.Organization_Id
And BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
And ( BIC.DISABLE_DATE IS NULL OR BIC.DISABLE_DATE > sysdate )
And MSI2.INVENTORY_ITEM_ID(+) = BIC.COMPONENT_ITEM_ID
And MSI2.Organization_Id(+) = X
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
)a3,
(Select
MSI1.Segment1 产品编码,
MSI1.Description 产品描述,
MSI2.Segment1 物料编码,
MSI2.Description 物料描述,
MSI2.PRIMARY_UOM_CODE 单位,
BIC.COMPONENT_QUANTITY 装配数量,
BIC.Last_Update_Date 更改日期,
bic.component_remarks 备注
From
INV.MTL_SYSTEM_ITEMS_B MSI1,
BOM.BOM_BILL_OF_MATERIALS BOM,
BOM.BOM_INVENTORY_COMPONENTS BIC,
INV.MTL_SYSTEM_ITEMS_B MSI2
Where
MSI1.Organization_Id = X
And BOM.ASSEMBLY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
And BOM.Organization_Id = MSI1.Organization_Id
And BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
And ( BIC.DISABLE_DATE IS NULL OR BIC.DISABLE_DATE > sysdate )
And MSI2.INVENTORY_ITEM_ID(+) = BIC.COMPONENT_ITEM_ID
And MSI2.Organization_Id(+) = X
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
)a4,
(Select
MSI1.Segment1 产品编码,
MSI1.Description 产品描述,
MSI2.Segment1 物料编码,
MSI2.Description 物料描述,
MSI2.PRIMARY_UOM_CODE 单位,
BIC.COMPONENT_QUANTITY 装配数量,
BIC.Last_Update_Date 更改日期,
bic.component_remarks 备注
From
INV.MTL_SYSTEM_ITEMS_B MSI1,
BOM.BOM_BILL_OF_MATERIALS BOM,
BOM.BOM_INVENTORY_COMPONENTS BIC,
INV.MTL_SYSTEM_ITEMS_B MSI2
Where
MSI1.Organization_Id = X
And BOM.ASSEMBLY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
And BOM.Organization_Id = MSI1.Organization_Id
And BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
And ( BIC.DISABLE_DATE IS NULL OR BIC.DISABLE_DATE > sysdate )
And MSI2.INVENTORY_ITEM_ID(+) = BIC.COMPONENT_ITEM_ID
And MSI2.Organization_Id(+) = X
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
)a5,
(Select
MSI1.Segment1 产品编码,
MSI1.Description 产品描述,
MSI2.Segment1 物料编码,
MSI2.Description 物料描述,
MSI2.PRIMARY_UOM_CODE 单位,
BIC.COMPONENT_QUANTITY 装配数量,
BIC.Last_Update_Date 更改日期,
bic.component_remarks 备注
From
INV.MTL_SYSTEM_ITEMS_B MSI1,
BOM.BOM_BILL_OF_MATERIALS BOM,
BOM.BOM_INVENTORY_COMPONENTS BIC,
INV.MTL_SYSTEM_ITEMS_B MSI2
Where
MSI1.Organization_Id = X
And BOM.ASSEMBLY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
And BOM.Organization_Id = MSI1.Organization_Id
And BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
And ( BIC.DISABLE_DATE IS NULL OR BIC.DISABLE_DATE > sysdate )
And MSI2.INVENTORY_ITEM_ID(+) = BIC.COMPONENT_ITEM_ID
And MSI2.Organization_Id(+) = X
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
)a6,
(Select
MSI1.Segment1 产品编码,
MSI1.Description 产品描述,
MSI2.Segment1 物料编码,
MSI2.Description 物料描述,
MSI2.PRIMARY_UOM_CODE 单位,
BIC.COMPONENT_QUANTITY 装配数量,
BIC.Last_Update_Date 更改日期,
bic.component_remarks 备注
From
INV.MTL_SYSTEM_ITEMS_B MSI1,
BOM.BOM_BILL_OF_MATERIALS BOM,
BOM.BOM_INVENTORY_COMPONENTS BIC,
INV.MTL_SYSTEM_ITEMS_B MSI2
Where
MSI1.Organization_Id = X
And BOM.ASSEMBLY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
And BOM.Organization_Id = MSI1.Organization_Id
And BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
And ( BIC.DISABLE_DATE IS NULL OR BIC.DISABLE_DATE > sysdate )
And MSI2.INVENTORY_ITEM_ID(+) = BIC.COMPONENT_ITEM_ID
And MSI2.Organization_Id(+) = X
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
)a7,
(Select
MSI1.Segment1 产品编码,
MSI1.Description 产品描述,
MSI2.Segment1 物料编码,
MSI2.Description 物料描述,
MSI2.PRIMARY_UOM_CODE 单位,
BIC.COMPONENT_QUANTITY 装配数量,
BIC.Last_Update_Date 更改日期,
bic.component_remarks 备注
From
INV.MTL_SYSTEM_ITEMS_B MSI1,
BOM.BOM_BILL_OF_MATERIALS BOM,
BOM.BOM_INVENTORY_COMPONENTS BIC,
INV.MTL_SYSTEM_ITEMS_B MSI2
Where
MSI1.Organization_Id = X
And BOM.ASSEMBLY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
And BOM.Organization_Id = MSI1.Organization_Id
And BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
And ( BIC.DISABLE_DATE IS NULL OR BIC.DISABLE_DATE > sysdate )
And MSI2.INVENTORY_ITEM_ID(+) = BIC.COMPONENT_ITEM_ID
And MSI2.Organization_Id(+) = X
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
)a8
where
a1.物料编码=a2.产品编码(+)
and a2.物料编码=a3.产品编码(+)
and a3.物料编码=a4.产品编码(+)
and a4.物料编码=a5.产品编码(+)
and a5.物料编码=a6.产品编码(+)
and a6.物料编码=a7.产品编码(+)
and a7.物料编码=a8.产品编码(+)
ORDER BY
a1.物料编码
, a2.物料编码
, a3.物料编码
, a4.物料编码
, a5.物料编码
, a6.物料编码
, a7.物料编码
最新文章
- 菜鸟Python学习笔记第一天:关于一些函数库的使用
- Rails 5 开发进阶
- linux中wc命令用法
- java 除法向上,向下取整
- Hibernate java.lang.NoSuchFieldError: INSTANCE
- Bitmap 与Drawable相互转换
- (原)ubuntu上安装dlib
- Avoid The Lakes
- 对用户控件(ascx)属性(property)赋值
- 【Excel】获取网页标题的VBA
- java虚拟机的内存分配与回收机制
- SOFA 源码分析 —— 服务引用过程
- c#批量抓取免费代理并验证有效性
- Django ORM中,如何使用Count来关联对象的子集数量
- jQuery根据元素值或元素下标来删除一个数组元素及数组对象方法列表
- Qt QLabel的使用
- hdu 4704 Sum 【费马小定理】
- C#中委托、事件和回调函数的理解
- Spring Boot 日志配置
- java中的jdk配置详解:
热门文章
- SPOJDIVCNT2: Counting Divisors(莫比乌斯反演)
- Qt 文件搜索(写入文本文件)
- Fedora15下搭建QT开发环境及编译QT
- Redis应用场景-整理
- IE input X 去掉文本框的叉叉和密码输入框的眼睛图标
- ArcEngine10在VS2010中编译问题
- 用VS2013+VELT-0.1.4进行海思平台 Linux内核 的开发
- EasyUI的下拉选择框控件方法被屏蔽处理方式
- Best Time to Buy and Sell Stock 解答
- Windows多线程同步系列之一-----互斥对象