SELECT

      ITM.SEGMENT1                         物料编码

     ,ITM.DESCRIPTION                     物料描述

     ,bom2.CREATION_DATE              创建日期

     ,BOM2.ALTERNATE_BOM_DESIGNATOR  替代BOM

     ,FU.description                           操作者

FROM INV.MTL_SYSTEM_ITEMS_B          ITM,

     apps.BOM_BILL_OF_MATERIALS     bom2,

     applsys.fnd_user  FU

WHERE

       ITM.INVENTORY_ITEM_STATUS_CODE <> 'Inactive'                        AND

       EXISTS

      (

         SELECT  COM.COMPONENT_ITEM_ID

         FROM  apps.BOM_INVENTORY_COMPONENTS COM,

               apps.BOM_BILL_OF_MATERIALS BOM

         WHERE

                ROWNUM=1                                                                          AND

         COM.EFFECTIVITY_DATE<=SYSDATE                                             AND

         (COM.DISABLE_DATE IS NULL OR COM.DISABLE_DATE>SYSDATE)  AND

               BOM.BILL_SEQUENCE_ID=COM.BILL_SEQUENCE_ID                 AND

               BOM.ASSEMBLY_ITEM_ID=ITM.INVENTORY_ITEM_ID                AND

               BOM.ORGANIZATION_ID=ITM.ORGANIZATION_ID)                   AND

               ITM.ORGANIZATION_ID =X                                                     AND

               FU.USER_ID=BOM2.CREATED_BY                                            AND

               bom2.ASSEMBLY_ITEM_ID=itm.inventory_item_id                    AND

               bom2.ORGANIZATION_ID =X                                                  AND

               ITM.SEGMENT1 LIKE '31%'