Home » SQL & PL/SQL » SQL & PL/SQL » Inventory Transfer Query
Inventory Transfer Query [message #260494] Mon, 20 August 2007 02:49
Abeer30
Messages: 110
Registered: March 2007
Senior Member
hello all, I 'm trying to write a sql that gives me the items transferred between subinventories. so far i have this sql:

select
distinct mmt.TRANSACTION_ID
,mmt.TRANSACTION_DATE
--,mmt.TRANSACTION_TYPE_ID
--,mtt.TRANSACTION_TYPE_NAME
,mmt.INVENTORY_ITEM_ID
,msi.DESCRIPTION
,mmt.ORGANIZATION_ID
,mmt.SUBINVENTORY_CODE
,mmt.LOCATOR_ID
,mil.SEGMENT1
,mil.SEGMENT2
,mil.SEGMENT3
,mil.SEGMENT4
,mmt.TRANSACTION_QUANTITY
,mmt.TRANSACTION_UOM
,mmt.ACTUAL_COST
,mmt.TRANSFER_TRANSACTION_ID
,mmt.TRANSACTION_SET_ID
,mmt.RCV_TRANSACTION_ID
,mmt.TRANSFER_ORGANIZATION_ID
,mmt.TRANSFER_SUBINVENTORY
,mmt.TRANSFER_LOCATOR_ID
from
mtl_material_transactions mmt
,MTL_SYSTEM_ITEMS_B msi
,MTL_ITEM_LOCATIONS mil
,MTL_TRANSACTION_TYPES mtt
where
mmt.INVENTORY_ITEM_ID(+) = msi.INVENTORY_ITEM_ID
and mmt.LOCATOR_ID=mil.INVENTORY_LOCATION_ID(+)
--mmt.TRANSFER_LOCATOR_ID=mil.INVENTORY_LOCATION_ID
and mmt.TRANSACTION_TYPE_ID=mtt.TRANSACTION_TYPE_ID(+)
and mmt.TRANSACTION_QUANTITY <= 0
--and mmt.SUBINVENTORY_CODE='yy'
--and mmt.TRANSFER_SUBINVENTORY='xx'

my problem is with the columns :mmt.LOCATOR_ID,mmt.TRANSFER_LOCATOR_ID and ,mil.SEGMENT1
,mil.SEGMENT2
,mil.SEGMENT3
,mil.SEGMENT4

as mmt.LOCATOR_ID is the locator transferred from and mmt.TRANSFER_LOCATOR_ID is used as the locator transferred to , the other columns are used to describe the locator segments,

the query above works fine for the mmt.LOCATOR_ID column and the segments also appear in the output. My poblem is with the mmt.TRANSFER_LOCATOR_ID column I didn't succeed to show it in my query correctly and when i tried putting them in two different queries and made a union between them the mmt.TRANSFER_LOCATOR_ID query didn't show me the segments when the the field is not nill.

I need also to differentiate between the segments columns when i use them for mmt.TRANSFER_LOCATOR_ID and mmt.LOCATOR_ID in order that i can map them to the report template.

Can u please help me....
Previous Topic: ORA-22275
Next Topic: How to Aggregate records in the same Table
Goto Forum:
  


Current Time: Thu Dec 08 20:20:40 CST 2016

Total time taken to generate the page: 0.05312 seconds