select
MSI.SEGMENT1 项目编码,
MSI.DESCRIPTION 描述,
MMT.SUBINVENTORY_CODE 子库,
mmt.transaction_quantity 交易数量,
mmt.creation_date 操作日期,
mmt.transaction_date 交易日期,
MTLV.LOT_NUMBER 批次号,
MIL.SEGMENT1 库位,
MTT.TRANSACTION_TYPE_NAME 交易类型名称,
MGD.DESCRIPTION 帐户别名,
MMT.TRANSACTION_REFERENCE 参考信息,
FU.DESCRIPTION 更新者
from
inv.Mtl_Material_Transactions MMT,
INV.MTL_SYSTEM_ITEMS_B MSI,
inv.mtl_generic_dispositions MGD,
inv.MTL_TRANSACTION_LOT_NUMBERS MTLV,
INV.MTL_TRANSACTION_TYPES MTT,
INV.MTL_SECONDARY_INVENTORIES MS1,
applsys.fnd_user FU,
INV.MTL_ITEM_LOCATIONS MIL
where
MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 6
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND MMT.TRANSACTION_SOURCE_ID = MGD.DISPOSITION_ID
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MTLV.TRANSACTION_ID(+) = MMT.TRANSACTION_ID
AND MSI.ORGANIZATION_ID = x
AND TO_CHAR(mmt.creation_date,('YYYY-MM-DD')) BETWEEN '&DATE1' AND '&DATE2'
AND MMT.CREATED_BY = FU.USER_ID
AND MMT.SUBINVENTORY_CODE = MS1.SECONDARY_INVENTORY_NAME
AND MS1.AVAILABILITY_TYPE = 1