INV Consigned Return Transactions

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Inventory Consigned Return Transactions Report
Application: Inventory
Source: Consigned Return Transactions Report (XML)
Short Name: INVCONRT_XML
DB package: INV_INVCONRT_XMLP_PKG
Run INV Consigned Return Transactions and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Date Required From 
 
 Date
Date Required To 
 
 Date
Item 
 
 LOV Oracle
Subinventory 
 
 LOV Oracle
Locator 
 
 Char
Owning Organization 
 
 LOV Oracle
Download
Related Blitz Reports
CAC Purchase Price Variance GL Account Analysis (Distributions) CST Miscellaneous Accrual Reconciliation PO Headers and Lines 11i GL Account Analysis PO Receiving Transactions Pipeline CST Uninvoiced Receipts CAC Open Purchase Orders CST AP and PO Accrual Reconciliation
Blitz Report™

Blitz Report™ provides multiple benefits: