ECC Inventory Management, Inventory Transaction Management

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: inv-transaction-management
Query Procedure: INV_ECC_TRANS_MGMT_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: inv_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
select
x.*
from
(
select * from (select x.* ,trn_dfv.*from (
	SELECT  map.ecc_spec_id
      , map.inventory_item_id
      , map.item
      , mstl.description item_description
      , map.revision
      , map.organization_id
      , map.organization_code
      , map.subinventory_code
      , map.locator_id
      , map.locator
      , map.lot_number
      , map.transaction_date
      , map.transaction_quantity
      , map.transaction_uom
      , map.transaction_header_id
      , map.source_code
      , map.source_line_id
      , map.transaction_mode
      , map.last_update_date
      , map.last_updated_by
      , map.request_id
      , map.item_value
      , map.primary_quantity
      , mtt.transaction_type_name transaction_type
      , map.transaction_type_id
      , map.transaction_action_id
      , map.transaction_source_type_id
      , map.transaction_source_id
      , map.shipment_num
      , map.expected_receipt_date
      , map.charge_account_id
      , map.transaction_source_name
      , map.demand_id
      , map.transfer_subinventory
      ,
        (
        SELECT  organization_code
        FROM    mtl_parameters
        WHERE   organization_id = map.transfer_organization
        ) transfer_organization
      , map.process_flag
      , map.error_explanation
      , map.cycle_count_entry_id
      , map.cycle_count_header_id
      , map.cycle_count_header_name
      , map.count_quantity
      , map.count_uom
      , map.count_date
      , map.employee_full_name
      , map.demand_source_name
      , map.demand_source_line_id
      , map.demand_source_type_id
      , map.demand_type
      , map.supply_line
      , map.supply_header
      , map.reservation_quantity
      , map.reservation_uom_code
      , map.requirement_date
      , map.line_item_quantity
      , map.line_item_uom
      , map.object_type
      , map.parent_object_type
      , map.parent_object_id
      , map.start_date_active
      , map.end_date_active
      , map.genealogy_origin
      , map.origin_txn_id
      , map.genealogy_type
      , map.referring_to
      , mstl.language
      , org_tl.name operating_unit
      , map.mti_interface_id
      , map.rcv_interface_transaction_id
      , map.cc_entry_interface_id
      , map.reservation_interface_id
      , map.batch_id
      , map.lot_control_code
      , map.serial_number_control_code
      , map.enable_lot_action
      , map.enable_serial_action
      , CASE
        WHEN    map.lpn_id IS NOT NULL
                THEN
                        (
                        SELECT  license_plate_number
                        FROM    wms_license_plate_numbers
                        WHERE   lpn_id = map.lpn_id
                        )
        END lpn
		, map.lot_transaction_quantity
FROM    inv_ecc_trans_mgmt_map map
      , mtl_system_items_tl mstl
      , mtl_transaction_types_tl mtt
      , org_organization_definitions ogd
      , hr_all_organization_units_tl org_tl
WHERE   map.organization_id = mstl.organization_id
AND     map.inventory_item_id = mstl.inventory_item_id
AND     map.organization_id = ogd.organization_id
AND     ogd.operating_unit = org_tl.organization_id
AND     mstl.language = org_tl.language
AND     map.transaction_type_id = mtt.transaction_type_id
AND     map.transaction_type_id IS NOT NULL
AND     map.organization_id=nvl('', map.organization_id)
AND     mstl.language = mtt.language
UNION
SELECT  map.ecc_spec_id
      , map.inventory_item_id
      , map.item
      , mstl.description item_description
      , map.revision
      , map.organization_id
      , map.organization_code
      , map.subinventory_code
      , map.locator_id
      , map.locator
      , map.lot_number
      , map.transaction_date
      , map.transaction_quantity
      , map.transaction_uom
      , map.transaction_header_id
      , map.source_code
      , map.source_line_id
      , map.transaction_mode
      , map.last_update_date
      , map.last_updated_by
      , map.request_id
      , map.item_value
      , map.primary_quantity
      , map.transaction_type transaction_type
      , map.transaction_type_id
      , map.transaction_action_id
      , map.transaction_source_type_id
      , map.transaction_source_id
      , map.shipment_num
      , map.expected_receipt_date
      , map.charge_account_id
      , map.transaction_source_name
      , map.demand_id
      , map.transfer_subinventory
      ,
        (
        SELECT  organization_code
        FROM    mtl_parameters
        WHERE   organization_id = map.transfer_organization
        ) transfer_organization
      , map.process_flag
      , map.error_explanation
      , map.cycle_count_entry_id
      , map.cycle_count_header_id
      , map.cycle_count_header_name
      , map.count_quantity
      , map.count_uom
      , map.count_date
      , map.employee_full_name
      , map.demand_source_name
      , map.demand_source_line_id
      , map.demand_source_type_id
      , map.demand_type
      , map.supply_line
      , map.supply_header
      , map.reservation_quantity
      , map.reservation_uom_code
      , map.requirement_date
      , map.line_item_quantity
      , map.line_item_uom
      , map.object_type
      , map.parent_object_type
      , map.parent_object_id
      , map.start_date_active
      , map.end_date_active
      , map.genealogy_origin
      , map.origin_txn_id
      , map.genealogy_type
      , map.referring_to
      , mstl.language
      , org_tl.name operating_unit
      , map.mti_interface_id
      , map.rcv_interface_transaction_id
      , map.cc_entry_interface_id
      , map.reservation_interface_id
      , map.batch_id
      , map.lot_control_code
      , map.serial_number_control_code
      , map.enable_lot_action
      , map.enable_serial_action
      , CASE
        WHEN    map.lpn_id IS NOT NULL
                THEN
                        (
                        SELECT  license_plate_number
                        FROM    wms_license_plate_numbers
                        WHERE   lpn_id = map.lpn_id
                        )
        END lpn
		, map.lot_transaction_quantity
FROM    inv_ecc_trans_mgmt_map map
      , mtl_system_items_tl mstl
      , org_organization_definitions ogd
      , hr_all_organization_units_tl org_tl
WHERE   map.organization_id = mstl.organization_id
AND     map.inventory_item_id = mstl.inventory_item_id
AND     map.organization_id = ogd.organization_id
AND     ogd.operating_unit = org_tl.organization_id
AND     map.organization_id=nvl('', map.organization_id)
AND     mstl.language = org_tl.language
AND     map.transaction_type IS NOT NULL
			union
			SELECT  map.ecc_spec_id
				  , map.inventory_item_id
				  , map.item
				  , mstl.description item_description
				  , map.revision
				  , map.organization_id
				  , map.organization_code
				  , map.subinventory_code
				  , map.locator_id
				  , map.locator
				  , map.lot_number
				  , map.transaction_date
				  , map.transaction_quantity
				  , map.transaction_uom
				  , map.transaction_header_id
				  , map.source_code
				  , map.source_line_id
				  , map.transaction_mode
				  , map.last_update_date
				  , map.last_updated_by
				  , map.request_id
				  , map.item_value
				  , map.primary_quantity
				  , NULL transaction_type
				  , NULL transaction_type_id
				  , map.transaction_action_id
				  , map.transaction_source_type_id
				  , map.transaction_source_id
				  , map.shipment_num
				  , map.expected_receipt_date
				  , map.charge_account_id
				  , map.transaction_source_name
				  , map.demand_id
				  , map.transfer_subinventory
				  ,
					(
					SELECT  organization_code
					FROM    mtl_parameters
					WHERE   organization_id = map.transfer_organization
					) transfer_organization
				  , map.process_flag
				  , map.error_explanation
				  , map.cycle_count_entry_id
				  , map.cycle_count_header_id
				  , map.cycle_count_header_name
				  , map.count_quantity
				  , map.count_uom
				  , map.count_date
				  , map.employee_full_name
				  , map.demand_source_name
				  , map.demand_source_line_id
				  , map.demand_source_type_id
				  , map.demand_type
				  , map.supply_line
				  , map.supply_header
				  , map.reservation_quantity
				  , map.reservation_uom_code
				  , map.requirement_date
				  , map.line_item_quantity
				  , map.line_item_uom
				  , map.object_type
				  , map.parent_object_type
				  , map.parent_object_id
				  , map.start_date_active
				  , map.end_date_active
				  , map.genealogy_origin
				  , map.origin_txn_id
				  , map.genealogy_type
				  , map.referring_to
				  , mstl.language
				  , org_tl.name operating_unit
				  , map.mti_interface_id
				  , map.rcv_interface_transaction_id
				  , map.cc_entry_interface_id
				  , map.reservation_interface_id
				  , map.batch_id
				  , map.lot_control_code
				  , map.serial_number_control_code
				  , map.enable_lot_action
				  , map.enable_serial_action
				   , CASE
							WHEN    map.lpn_id IS NOT NULL
                             THEN
                                (
                                SELECT  license_plate_number
                                FROM    wms_license_plate_numbers
                                WHERE   lpn_id = map.lpn_id
                                )
                            END lpn
				 , map.lot_transaction_quantity
			FROM    inv_ecc_trans_mgmt_map map
				  , mtl_system_items_tl mstl
				  , org_organization_definitions ogd
				  , hr_all_organization_units_tl org_tl
			WHERE   map.organization_id = mstl.organization_id
			AND     map.inventory_item_id = mstl.inventory_item_id
			AND     map.organization_id = ogd.organization_id
			AND     ogd.operating_unit = org_tl.organization_id
			AND     map.organization_id=nvl('', map.organization_id)
			AND     mstl.language = org_tl.language
			AND     map.transaction_type_id IS NULL
			AND     map.transaction_type IS NULL) x  ,(select "ROW_ID" "'INV_TRNINTERFACE_ROW_ID'","CONTEXT" "'INV_TRNINTERFACE_CONTEXT'","CONCATENATED_SEGMENTS" "'INV_TRNINTERFACE_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",NULL "CONCATENATED_SEGMENTS" from MTL_TRANSACTIONS_INTERFACE )) trn_dfv, mtl_transactions_interface mti where 1=1 and mti.rowid = trn_dfv."'INV_TRNINTERFACE_ROW_ID'"(+)
                                   and x.mti_interface_id = mti.transaction_interface_id(+))
  PIVOT (max(ITEM_DESCRIPTION) as ITEM_DESCRIPTION , max(TRANSACTION_TYPE) as
     TRANSACTION_TYPE , max(OPERATING_UNIT) as
     OPERATING_UNIT for LANGUAGE in ('US' "US"))
) x
where
2=2
Parameter NameSQL textValidation
Organization Code
x.organization_code=:organization_code
LOV
Download
Related Blitz Reports
ECC Inventory Management, Inventory Trace Sales Orders INV Onhand Quantities PO Receiving Transactions Register INV Items Yanmar - Discount on order lines check CAC Inventory Lot and Locator OPM Value (Period-End) PPF_WP3_OM_DETAILS ECC Inventory Management, Inbound Details CAC Inventory to G/L Reconciliation (Restricted by Org Access)
Blitz Report™