Oracle EBS-SQL (INV-5):检查期间拉式物料领用记录数.sql

select
         FU.description                                 操作者,
         KK.DESCRIPTION                             仓管员,
         MSI.SEGMENT1                             项目编码,
         MSI.DESCRIPTION                              描述,
         MMT.SUBINVENTORY_CODE          目标子库,
         MMT.TRANSFER_SUBINVENTORY    转移子库,
         mmt.transaction_quantity              交易数量,
         MUM.UNIT_OF_MEASURE_TL              单位,
         mmt.transaction_date                  交易日期,
         MIL.SEGMENT1                                  库位,
         mtt.transaction_type_name                类型,
         mtr.REASON_NAME                            原因,
         MTR.DESCRIPTION                        原因描述
from
         inv.Mtl_Material_Transactions           MMT,
         INV.MTL_UNITS_OF_MEASURE_TL     MUM,
         INV.MTL_SYSTEM_ITEMS_B               MSI,
         applsys.fnd_user                                 FU,
         INV.MTL_ITEM_LOCATIONS                MIL,
         inv.MTL_TRANSACTION_REASONS       mtr,
         (SELECT

MIC.INVENTORY_ITEM_ID,

MC.SEGMENT1,

MCT.DESCRIPTION
FROM inv.MTL_ITEM_CATEGORIES MIC,

inv.MTL_CATEGORIES_B MC,

inv.MTL_CATEGORIES_TL MCT
WHERE MIC.CATEGORY_ID = MC.CATEGORY_ID

AND MC.STRUCTURE_ID = 50479

               AND MCT.LANGUAGE='ZHS' AND
               AND MCT.CATEGORY_ID=MC.CATEGORY_ID
               AND MIC.ORGANIZATION_ID = X) KK,
               INV.MTL_TRANSACTION_TYPES   mtt
WHERE
            MSI.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID
  AND   MSI.ORGANIZATION_ID=MMT.ORGANIZATION_ID
  AND    MUM.UOM_CODE=MMT.TRANSACTION_UOM
  AND   MMT.LOCATOR_ID=MIL.INVENTORY_LOCATION_ID(+)
  AND   MMT.ORGANIZATION_ID=MIL.ORGANIZATION_ID(+)
  AND    mmt.transaction_type_id=mtt.transaction_type_id(+)
  AND   MSI.ORGANIZATION_ID=X
  AND   MMT.REASON_ID=MTR.REASON_ID(+)
  AND    MSI.INVENTORY_ITEM_ID = KK.INVENTORY_ITEM_ID(+)
  AND    mmt.transaction_TYPE_id=100       ----取系统设定的拉式发料对应的子库存转移类型ID，例如：k.拉式发料
AND    mmt.transaction_quantity>0
AND    FU.USER_ID=MMT.CREATED_BY
AND   (mmt.transaction_date between '01/01/20** 00:00:00' and '01/31/20** 23:59:59')   --时间为月/日/年时:分:秒