INV Consigned Return Transactions

select MSI.SEGMENT1 ITEM, 
                      MMT.SUBINVENTORY_CODE SUBINV, 
                      mil.concatenated_segments LOCATOR, 
                      pv.VENDOR_NAME VENDOR_NAME, 
                      MMT.TRANSACTION_QUANTITY TRANSACTION_QUANTITY, 
                      MMT.TRANSACTION_UOM TRANSACTION_UOM, 
                      to_char(MMT.TRANSACTION_DATE, 'DD-MON-YYYY HH:MI:SS AM') TRANSACTION_DATE,
                      to_char(trunc(PHA.CREATION_DATE), 'DD-MON-YYYY') BILLING_PERIOD_END_DATE,
                      PHA1.SEGMENT1 PO_NUMBER,
                      PHA.SEGMENT1 RELEASE_NUMBER,
					  fu.user_name USER_NAME
                 from MTL_CONSUMPTION_TRANSACTIONS MCT,
                      MTL_CONSUMPTION_TRANSACTIONS PMCT,
                      MTL_MATERIAL_TRANSACTIONS MMT,
                      MTL_SYSTEM_ITEMS_B MSI,
					  mtl_item_locations_kfv mil,					  
                      PO_VENDOR_SITES_ALL PS, 
                      PO_VENDORS PV,
                      PO_HEADERS_ALL PHA,
                      PO_HEADERS_ALL PHA1,
					  fnd_user fu
                where MCT.TRANSACTION_ID = MMT.TRANSACTION_ID
                  and MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
                  and MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
				  and nvl(MMT.LOCATOR_ID, -999) = mil.inventory_location_id(+)
                  and PV.VENDOR_ID = PS.VENDOR_ID
                  and PS.VENDOR_SITE_ID = mmt.XFR_OWNING_ORGANIZATION_ID
                  and mmt.transaction_action_id = 6
                  and MMT.TRANSACTION_SOURCE_TYPE_ID = 13
				  and mmt.created_by = fu.user_id
                  and MCT.PARENT_TRANSACTION_ID = PMCT.TRANSACTION_ID
                  and PMCT.CONSUMPTION_PO_HEADER_ID is not null
				  and PMCT.CONSUMPTION_PROCESSED_FLAG = 'Y'
                  and PMCT.CONSUMPTION_PO_HEADER_ID = PHA.PO_HEADER_ID
                  and PMCT.TRANSACTION_SOURCE_ID = PHA1.PO_HEADER_ID
				  and MMT.ORGANIZATION_ID = :P_ORG_ID
				  and MMT.transaction_date between trunc(:P_DATE_FROM) and trunc(:P_DATE_TO)
				  and &P_WHERE
                  union
                  select MSI.SEGMENT1 ITEM, 
                      MMT.SUBINVENTORY_CODE SUBINV, 
                      mil.concatenated_segments LOCATOR, 
                      pv.VENDOR_NAME VENDOR_NAME, 
                      MMT.TRANSACTION_QUANTITY TRANSACTION_QUANTITY, 
                      MMT.TRANSACTION_UOM TRANSACTION_UOM, 
                      to_char(MMT.TRANSACTION_DATE, 'DD-MON-YYYY HH:MI:SS AM') TRANSACTION_DATE,
                      to_char(trunc(PHA.CREATION_DATE), 'DD-MON-YYYY') BILLING_PERIOD_END_DATE,
                      PHA.SEGMENT1 PO_NUMBER,
                      to_Char(PRA.RELEASE_NUM) RELEASE_NUMBER,
					  fu.user_name USER_NAME
                 from MTL_CONSUMPTION_TRANSACTIONS MCT,
                      MTL_CONSUMPTION_TRANSACTIONS PMCT,
                      MTL_MATERIAL_TRANSACTIONS MMT,
                      MTL_SYSTEM_ITEMS_B MSI,
					  mtl_item_locations_kfv mil,
                      PO_VENDOR_SITES_ALL PS, 
                      PO_VENDORS PV,
                      PO_HEADERS_ALL PHA,
                      PO_RELEASES_ALL PRA,
					  fnd_user fu
                where MCT.TRANSACTION_ID = MMT.TRANSACTION_ID
                  and MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
                  and MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
				  and nvl(MMT.LOCATOR_ID, -999) = mil.inventory_location_id(+)
                  and PV.VENDOR_ID = PS.VENDOR_ID
                  and PS.VENDOR_SITE_ID = mmt.XFR_OWNING_ORGANIZATION_ID
                  and mmt.transaction_action_id = 6
                  and MMT.TRANSACTION_SOURCE_TYPE_ID = 13
				  and mmt.created_by = fu.user_id
                  and MCT.PARENT_TRANSACTION_ID = PMCT.TRANSACTION_ID
                  and PMCT.CONSUMPTION_PROCESSED_FLAG = 'Y'
				  and PMCT.CONSUMPTION_RELEASE_ID is not null
                  and PMCT.TRANSACTION_SOURCE_ID = PHA.PO_HEADER_ID
                  and PMCT.CONSUMPTION_RELEASE_ID = PRA.PO_RELEASE_ID
                  and MMT.ORGANIZATION_ID = :P_ORG_ID
				  and MMT.transaction_date between trunc(:P_DATE_FROM) and trunc(:P_DATE_TO)
			      and &P_WHERE
				  union
                  select MSI.SEGMENT1 ITEM, 
                      MMT.SUBINVENTORY_CODE SUBINV, 
                      mil.concatenated_segments LOCATOR, 
                      pv.VENDOR_NAME VENDOR_NAME, 
                      MMT.TRANSACTION_QUANTITY TRANSACTION_QUANTITY, 
                      MMT.TRANSACTION_UOM TRANSACTION_UOM, 
                      to_char(MMT.TRANSACTION_DATE, 'DD-MON-YYYY HH:MI:SS AM') TRANSACTION_DATE,
                      null BILLING_PERIOD_END_DATE,
                      PHA.SEGMENT1 PO_NUMBER,
                      null RELEASE_NUMBER,
					  fu.user_name USER_NAME
                 from MTL_CONSUMPTION_TRANSACTIONS MCT,
                      MTL_CONSUMPTION_TRANSACTIONS PMCT,
                      MTL_MATERIAL_TRANSACTIONS MMT,
                      MTL_SYSTEM_ITEMS_B MSI,
					  mtl_item_locations_kfv mil,
                      PO_VENDOR_SITES_ALL PS, 
                      PO_VENDORS PV,
                      PO_HEADERS_ALL PHA,
					  PO_LINES_ALL PLA,
					  fnd_user fu
                where MCT.TRANSACTION_ID = MMT.TRANSACTION_ID
                  and MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
                  and MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
				  and nvl(MMT.LOCATOR_ID, -999) = mil.inventory_location_id(+)
                  and PV.VENDOR_ID = PS.VENDOR_ID
                  and PS.VENDOR_SITE_ID = mmt.XFR_OWNING_ORGANIZATION_ID
                  and mmt.transaction_action_id = 6
                  and MMT.TRANSACTION_SOURCE_TYPE_ID = 13
				  and mmt.created_by = fu.user_id
                  and MCT.PARENT_TRANSACTION_ID = PMCT.TRANSACTION_ID
                  and PMCT.CONSUMPTION_PROCESSED_FLAG = 'N'
                  and PMCT.PO_LINE_ID = PLA.PO_LINE_ID
				  and PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
                  and MMT.ORGANIZATION_ID = :P_ORG_ID
				  and MMT.transaction_date between trunc(:P_DATE_FROM) and trunc(:P_DATE_TO)
			      and &P_WHERE
				  order by ITEM, BILLING_PERIOD_END_DATE