select
to_char(mmt.transaction_date,'MM-YYYY')
,decode(mmt.organization_id,93,'股份',99,'集团')
,msi.attribute1
,decode(mmt.transaction_type_id,18,'PO 接收',36,'向供应商退货')
,msi.inventory_item_id ITEM_ID
,msi.segment1 物料编码
,msi.description 物料说明
,inv.secondary_inventory_name 子库编码
,rsh.receipt_num 单据号
,decode(mmt.transaction_type_id,18,to_number(rt.attribute14),36,to_number(rt.attribute14))
,decode(mmt.transaction_type_id,18,mmt.transaction_quantity,36,-mmt.transaction_quantity)
,mmt.transaction_uom 单位
,to_char(mmt.transaction_date,'YYYY-MM-DD') 业务时间
,mmt.created_by created_by
from
rcv_transactions rt
,rcv_shipment_headers rsh
,mtl_material_transactions mmt
,mtl_system_items_b msi
,mtl_secondary_inventories inv
,mtl_transaction_types mtt
where mmt.rcv_transaction_id=rt.transaction_id
and rt.shipment_header_id=rsh.shipment_header_id
and msi.organization_id=94
and mmt.inventory_item_id=msi.inventory_item_id
and mmt.transaction_type_id=mtt.transaction_type_id
and rt.po_line_location_id is not null
and mmt.rcv_transaction_id is not null
--and rt.transaction_type in ('DELIVER','RETURN TO VENDOR')
and mmt.subinventory_code=inv.secondary_inventory_name
and mtt.transaction_type_id in (18,36)
and (msi.attribute1=1 or msi.attribute1=3)
--and mmt.created_by=-1
and mmt.transaction_date between sysdate-60 and sysdate
怎么进一步优化，请高手帮忙
|
