Select Moq.Organization_Id,
Mp.Organization_Code Org,
Msib.Segment1,
Msib.Description,
Msib.Inventory_Item_Id,
Msib.Item_Type,
Mln.Age,
Moq.Lot_Number,
Moq.Subinventory_Code Subinv,
Sum(Moq.Primary_Transaction_Quantity) Qty,
Mil.Segment1 || ‘-‘ || Mil.Segment2 || ‘-‘ || Mil.Segment3 || ‘-‘ || Mil.Segment4 Locator,
Flv.Meaning Item_Type_Code,
Xxcat_Get_Material_Vendor(Moq.Lot_Number, Msib.Inventory_Item_Id) Vendor_Name
From Mtl_Onhand_Quantities_Detail Moq,
Mtl_System_Items_b Msib,
Mtl_Parameters Mp,
Mtl_Item_Locations Mil,
Mtl_Lot_Numbers Mln,
Fnd_Lookup_Values_Vl Flv
Where 1 = 1
And Moq.Inventory_Item_Id = Msib.Inventory_Item_Id
And Moq.Organization_Id = Msib.Organization_Id
And Mp.Organization_Id = Msib.Organization_Id
And Moq.Subinventory_Code = Mil.Subinventory_Code(+)
And Moq.Locator_Id = Mil.Inventory_Location_Id(+)
And Mln.Inventory_Item_Id = Msib.Inventory_Item_Id
And Mln.Organization_Id = Msib.Organization_Id
And Mln.Lot_Number = Moq.Lot_Number
And Moq.Lot_Number Is Not Null
And Flv.Lookup_Type = ‘ITEM_TYPE’
And Flv.Lookup_Code = Msib.Item_Type
And Mp.Organization_Code = ‘CAT’
Group By Moq.Organization_Id,
Mp.Organization_Code,
Msib.Segment1,
Msib.Description,
Msib.Inventory_Item_Id,
Msib.Item_Type,
Mln.Age,
Moq.Lot_Number,
Moq.Subinventory_Code,
Moq.Inventory_Item_Id,
Mil.Segment1 || ‘-‘ || Mil.Segment2 || ‘-‘ || Mil.Segment3 || ‘-‘ || Mil.Segment4,
Flv.Meaning
Union
Select Moq.Organization_Id,
Mp.Organization_Code Org,
Msib.Segment1,
Msib.Description,
Msib.Inventory_Item_Id,
Msib.Item_Type,
Null Age,
Null Lot_Number,
Moq.Subinventory_Code Subinv,
Sum(Moq.Primary_Transaction_Quantity) Qty,
Null Locator,
Flv.Meaning Item_Type_Code,
Xxcat_Get_Material_Vendor(Null, Msib.Inventory_Item_Id) Vendor_Name
From Mtl_Onhand_Quantities_Detail Moq,
Mtl_System_Items_b Msib,
Mtl_Parameters Mp,
Mtl_Secondary_Inventories Msi,
Fnd_Lookup_Values_Vl Flv
Where Moq.Inventory_Item_Id = Msib.Inventory_Item_Id
And Moq.Organization_Id = Msib.Organization_Id
And Mp.Organization_Id = Msib.Organization_Id
And Msi.Secondary_Inventory_Name = Moq.Subinventory_Code
And Msi.Organization_Id = Mp.Organization_Id
And Moq.Lot_Number Is Null
And Flv.Lookup_Type = ‘ITEM_TYPE’
And Flv.Lookup_Code = Msib.Item_Type
And Mp.Organization_Code = ‘CAT’
Group By Moq.Organization_Id,
Mp.Organization_Code,
Msib.Segment1,
Msib.Description,
Msib.Inventory_Item_Id,
Msib.Item_Type,
Moq.Subinventory_Code,
Flv.Meaning,
Msi.Location_Id;