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.物料编码
