FND Design Data [Home] [Help]

View: MTL_MOVEMENT_STATISTICS_V

Product: INV - Inventory
Description:
Implementation/DBA Data: Not implemented in this database
View Text

SELECT MMS.ROWID ROW_ID
, MMS.MOVEMENT_ID
, MMS.ORGANIZATION_ID
, MMS.MOVEMENT_STATUS
, FL0.MEANING MOVEMENT_STATUS_MEANING
, MMS.MOVEMENT_TYPE
, FL1.MEANING MOVEMENT_TYPE_MEANING
, MMS.DOCUMENT_SOURCE_TYPE
, FL2.MEANING DOCUMENT_SOURCE_TYPE_MEANING
, MMS.ENTITY_ORG_ID
, HLE.NAME LEGAL_ENTITY
, MMS.TRANSACTION_DATE
, MMS.BILL_TO_CUSTOMER_ID
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.BILL_TO_NAME
, RC1.CUSTOMER_NAME) CUSTOMER_BILL_TO
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.BILL_TO_NUMBER
, RC1.CUSTOMER_NUMBER) CUSTOMER_BILL_TO_NUMBER
, MMS.BILL_TO_SITE_USE_ID
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.BILL_TO_SITE
, RSU1.LOCATION) CUSTOMER_BILL_TO_SITE
, MMS.INVOICE_BATCH_ID
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_BATCH_REFERENCE
, AB.BATCH_NAME) BATCH_NAME
, MMS.INVOICE_ID
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_REFERENCE
, 'SO'
, RCT.TRX_NUMBER
, AI.INVOICE_NUM) INVOICE_NUM
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_DATE_REFERENCE
, 'SO'
, RCT.TRX_DATE
, AI.INVOICE_DATE) INVOICE_DATE_REFERENCE
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, NULL
, 'SO'
, RCT.DOC_SEQUENCE_ID
, AI.DOC_SEQUENCE_ID) DOCUMENT_NUM
, MMS.CUSTOMER_TRX_LINE_ID
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_LINE_REFERENCE
, RCTL.LINE_NUMBER) INVOICE_LINE_NUM
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_QUANTITY
, NVL(MMS.INVOICE_QUANTITY
, RCTL.QUANTITY_INVOICED)) INVOICE_QUANTITY
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_UNIT_PRICE
, NVL(MMS.INVOICE_UNIT_PRICE
, RCTL.UNIT_SELLING_PRICE)) INVOICE_UNIT_PRICE
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_LINE_EXT_VALUE
, NVL(MMS.INVOICE_LINE_EXT_VALUE
, RCTL.EXTENDED_AMOUNT)) INVOICE_EXT_VALUE
, MMS.FROM_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE FROM_ORG_CODE
, HOU1.NAME FROM_ORG_NAME
, MMS.TO_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE TO_ORG_CODE
, HOU2.NAME TO_ORG_NAME
, MMS.VENDOR_ID
, MMS.VENDOR_NAME VENDOR
, PV.VENDOR_NAME VENDOR_NONMISC
, MMS.VENDOR_NUMBER VENDOR_NUM
, PV.SEGMENT1 VENDOR_NUM_NONMISC
, MMS.VENDOR_SITE_ID
, MMS.VENDOR_SITE VENDOR_SITE
, PVS.VENDOR_SITE_CODE VENDOR_SITE_NONMISC
, MMS.CUSTOMER_NAME CUSTOMER
, RC2.CUSTOMER_NAME CUSTOMER_NONMISC
, MMS.CUSTOMER_NUMBER CUSTOMER_NUM
, RC2.CUSTOMER_NUMBER CUSTOMER_NUM_NONMISC
, MMS.CUSTOMER_LOCATION CUSTOMER_LOCATION
, RSU2.LOCATION CUSTOMER_LOCATION_NONMISC
, MMS.PO_HEADER_ID
, MMS.PO_LINE_ID
, MMS.PO_LINE_LOCATION_ID
, MMS.ORDER_HEADER_ID
, MMS.ORDER_LINE_ID
, MMS.REQUISITION_HEADER_ID
, MMS.REQUISITION_LINE_ID
, MMS.PICKING_LINE_ID
, MMS.PICKING_LINE_DETAIL_ID
, MMS.SHIPMENT_HEADER_ID
, MMS.SHIPMENT_LINE_ID
, MMS.SHIP_TO_CUSTOMER_ID
, MMS.SHIP_TO_SITE_USE_ID
, MMS.DOCUMENT_REFERENCE
, MMS.DOCUMENT_LINE_REFERENCE
, MMS.SHIPMENT_REFERENCE
, MMS.SHIPMENT_LINE_REFERENCE
, MMS.PICK_SLIP_REFERENCE
, MMS.RECEIPT_REFERENCE
, MMS.TRANSACTING_FROM_ORG
, MMS.TRANSACTING_TO_ORG
, MMS.INVOICE_BATCH_REFERENCE
, MMS.INVOICE_REFERENCE
, MMS.INVOICE_LINE_REFERENCE
, MMS.PARENT_MOVEMENT_ID
, MMS.CURRENCY_CODE
, MMS.INVENTORY_ITEM_ID
, MMS.ITEM_DESCRIPTION
, MMS.ITEM_COST
, MMS.TRANSACTION_UOM_CODE
, MMS.CATEGORY_ID
, MMS.COMMODITY_CODE
, MMS.COMMODITY_DESCRIPTION
, MMS.TRANSACTION_QUANTITY
, MMS.PRIMARY_QUANTITY
, MMS.DOCUMENT_UNIT_PRICE
, MMS.DOCUMENT_LINE_EXT_VALUE
, MMS.TRANSACTION_NATURE
, FL4.MEANING TRANSACTION_NATURE_MEANING
, MMS.DELIVERY_TERMS
, FL5.MEANING DELIVERY_TERMS_MEANING
, MMS.TRANSPORT_MODE
, FL6.MEANING TRANSPORT_MODE_MEANING
, MMS.PORT
, FL7.MEANING PORT_MEANING
, MMS.AREA
, FL8.MEANING AREA_MEANING
, MMS.STAT_TYPE
, FL9.MEANING MOVEMENT_NATURE_MEANING
, MMP.WEIGHT_UOM_CODE
, MMS.WEIGHT_METHOD
, MMS.UNIT_WEIGHT
, MMS.TOTAL_WEIGHT
, MMS.STAT_METHOD
, MMS.STAT_ADJ_PERCENT
, MMS.STAT_ADJ_AMOUNT
, MMS.STAT_EXT_VALUE
, MMS.COMMENTS
, MMS.ALTERNATE_QUANTITY
, MMS.ALTERNATE_UOM_CODE
, MMS.OUTSIDE_CODE
, FL10.MEANING OUTSIDE_CODE_MEANING
, FT1.TERRITORY_SHORT_NAME DISPATCH_COUNTRY
, FT2.TERRITORY_SHORT_NAME DESTINATION_COUNTRY
, FT3.TERRITORY_SHORT_NAME ORIGIN_COUNTRY
, MMS.DISPATCH_TERRITORY_CODE
, MMS.DESTINATION_TERRITORY_CODE
, MMS.ORIGIN_TERRITORY_CODE
, MMS.OUTSIDE_UNIT_PRICE
, MMS.OUTSIDE_EXT_VALUE
, MMS.REPORT_REFERENCE
, MMS.PERIOD_NAME
, MMS.LAST_UPDATE_DATE
, MMS.LAST_UPDATED_BY
, MMS.CREATION_DATE
, MMS.CREATED_BY
, MMS.LAST_UPDATE_LOGIN
, MMS.ATTRIBUTE_CATEGORY
, MMS.ATTRIBUTE1
, MMS.ATTRIBUTE2
, MMS.ATTRIBUTE3
, MMS.ATTRIBUTE4
, MMS.ATTRIBUTE5
, MMS.ATTRIBUTE6
, MMS.ATTRIBUTE7
, MMS.ATTRIBUTE8
, MMS.ATTRIBUTE9
, MMS.ATTRIBUTE10
, MMS.ATTRIBUTE11
, MMS.ATTRIBUTE12
, MMS.ATTRIBUTE13
, MMS.ATTRIBUTE14
, MMS.ATTRIBUTE15
, MMS.ZONE_CODE
, MMS.EDI_SENT_FLAG
, MMS.MOVEMENT_NATURE_CODE
, MMS.MOVEMENT_AMOUNT
, FT4.TERRITORY_SHORT_NAME TRIANGULATION_COUNTRY
, MMS.TRIANGULATION_COUNTRY_CODE TRIANGULATION_COUNTRY_CODE
, MMS.CSA_CODE
, FL11.MEANING CSA_MEANING
, MMS.OIL_REFERENCE_CODE
, FL12.MEANING OIL_REFERENCE_MEANING
, MMS.CONTAINER_TYPE_CODE
, FL13.MEANING CONTAINER_TYPE_MEANING
, MMS.FLOW_INDICATOR_CODE
, FL14.MEANING FLOW_INDICATOR_MEANING
, MMS.AFFILIATION_REFERENCE_CODE AFFILIATION_REFERENCE_CODE
, FL15.MEANING AFFILIATION_REFERENCE_MEANING
, MMS.ORIGIN_TERRITORY_EU_CODE
, MMS.DESTINATION_TERRITORY_EU_CODE
, MMS.DISPATCH_TERRITORY_EU_CODE
, MMS.SET_OF_BOOKS_PERIOD
, MMS.EDI_TRANSACTION_DATE
, MMS.EDI_TRANSACTION_REFERENCE
, MMS.TARIC_CODE
, MMS.PREFERENCE_CODE
, MMS.TRIANGULATION_COUNTRY_EU_CODE
, MMS.DISTRIBUTION_LINE_NUMBER
, MMS.RCV_TRANSACTION_ID
, MMS.MTL_TRANSACTION_ID
, MMS.USAGE_TYPE
, MEZ.ZONE_DISPLAY_NAME ZONE_NAME
FROM MTL_MOVEMENT_STATISTICS MMS
, MTL_STAT_TYPE_USAGES MMP
, FND_LOOKUPS FL0
, FND_LOOKUPS FL1
, FND_LOOKUPS FL2
, HR_LEGAL_ENTITIES HLE
, HR_ORGANIZATION_UNITS HOU1
, HR_ORGANIZATION_UNITS HOU2
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, RA_CUSTOMERS RC1
, RA_SITE_USES RSU1
, AP_BATCHES AB
, AP_INVOICES AI
, RA_CUSTOMER_TRX RCT
, RA_CUSTOMER_TRX_LINES RCTL
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
, RA_CUSTOMERS RC2
, RA_SITE_USES RSU2
, FND_LOOKUPS FL4
, FND_LOOKUPS FL5
, FND_LOOKUPS FL6
, FND_LOOKUPS FL7
, FND_LOOKUPS FL8
, FND_LOOKUPS FL9
, FND_LOOKUPS FL10
, FND_TERRITORIES_VL FT1
, FND_TERRITORIES_VL FT2
, FND_TERRITORIES_VL FT3
, FND_LOOKUPS FL11
, FND_LOOKUPS FL12
, FND_LOOKUPS FL13
, FND_LOOKUPS FL14
, FND_LOOKUPS FL15
, FND_TERRITORIES_VL FT4
, MTL_ECONOMIC_ZONES_VL MEZ
WHERE FL0.LOOKUP_TYPE = 'MVT_STATUSES'
AND FL0.LOOKUP_CODE = MMS.MOVEMENT_STATUS
AND FL1.LOOKUP_TYPE = 'MVT_MOVEMENT_TYPE'
AND FL1.LOOKUP_CODE = MMS.MOVEMENT_TYPE
AND FL2.LOOKUP_TYPE = 'MVT_SOURCE_DOCUMENT_TYPES'
AND FL2.LOOKUP_CODE = MMS.DOCUMENT_SOURCE_TYPE
AND HLE.ORGANIZATION_ID = MMS.ENTITY_ORG_ID
AND MMP.LEGAL_ENTITY_ID = MMS.ENTITY_ORG_ID
AND MP1.ORGANIZATION_ID (+) = MMS.FROM_ORGANIZATION_ID
AND HOU1.ORGANIZATION_ID (+) = MP1.ORGANIZATION_ID
AND MP2.ORGANIZATION_ID (+) = MMS.TO_ORGANIZATION_ID
AND HOU2.ORGANIZATION_ID (+) = MP2.ORGANIZATION_ID
AND RC1.CUSTOMER_ID(+) = MMS.BILL_TO_CUSTOMER_ID
AND RSU1.SITE_USE_ID(+) = MMS.BILL_TO_SITE_USE_ID
AND AB.BATCH_ID(+) = MMS.INVOICE_BATCH_ID
AND AI.INVOICE_ID(+) = MMS.INVOICE_ID
AND RCT.CUSTOMER_TRX_ID(+) = MMS.INVOICE_ID
AND RCTL.CUSTOMER_TRX_LINE_ID(+) = MMS.CUSTOMER_TRX_LINE_ID
AND PV.VENDOR_ID(+) = MMS.VENDOR_ID
AND PVS.VENDOR_SITE_ID(+) = MMS.VENDOR_SITE_ID
AND RC2.CUSTOMER_ID(+) = MMS.SHIP_TO_CUSTOMER_ID
AND RSU2.SITE_USE_ID(+) = MMS.SHIP_TO_SITE_USE_ID
AND MEZ.ZONE_CODE(+) = MMS.ZONE_CODE
AND MMP.ZONE_CODE = MMS.ZONE_CODE
AND UPPER(MMP.USAGE_TYPE) = UPPER(MMS.USAGE_TYPE)
AND UPPER(MMP.STAT_TYPE) = UPPER(MMS.STAT_TYPE)
AND FT1.TERRITORY_CODE(+) = MMS.DISPATCH_TERRITORY_CODE
AND FT2.TERRITORY_CODE(+) = MMS.DESTINATION_TERRITORY_CODE
AND FT3.TERRITORY_CODE(+) = MMS.ORIGIN_TERRITORY_CODE
AND FL4.LOOKUP_TYPE(+) = 'MVT_TRANSACTION_NATURE'
AND FL4.LOOKUP_CODE(+) = MMS.TRANSACTION_NATURE
AND FL5.LOOKUP_TYPE(+) = 'MVT_DELIVERY_TERMS'
AND FL5.LOOKUP_CODE(+) = MMS.DELIVERY_TERMS
AND FL6.LOOKUP_TYPE(+) = 'MVT_TRANSPORT_MODE'
AND FL6.LOOKUP_CODE(+) = MMS.TRANSPORT_MODE
AND FL7.LOOKUP_TYPE(+) = 'MVT_PORT'
AND FL7.LOOKUP_CODE(+) = MMS.PORT
AND FL8.LOOKUP_TYPE(+) = 'MVT_AREA'
AND FL8.LOOKUP_CODE(+) = MMS.AREA
AND FL9.LOOKUP_TYPE(+) = 'MVT_STATISTICAL_TYPE'
AND FL9.LOOKUP_CODE(+) = MMS.MOVEMENT_NATURE_CODE
AND FL10.LOOKUP_TYPE(+) = 'MVT_OUTSIDE_CODE'
AND FL10.LOOKUP_CODE(+) = MMS.OUTSIDE_CODE
AND FL11.LOOKUP_TYPE(+) = 'MVT_SUPPLY_REFERENCE'
AND FL11.LOOKUP_CODE(+) = MMS.CSA_CODE
AND FL12.LOOKUP_TYPE(+) = 'MVT_OIL_REFERENCE'
AND FL12.LOOKUP_CODE(+) = MMS.OIL_REFERENCE_CODE
AND FL13.LOOKUP_TYPE(+) = 'MVT_TRANSPORT_CONTAINER'
AND FL13.LOOKUP_CODE(+) = MMS.CONTAINER_TYPE_CODE
AND FL14.LOOKUP_TYPE(+) = 'MVT_FLOW_INDICATOR'
AND FL14.LOOKUP_CODE(+) = MMS.FLOW_INDICATOR_CODE
AND FL15.LOOKUP_TYPE(+) = 'MVT_AFFILIATION_REFERENCE'
AND FL15.LOOKUP_CODE(+) = MMS.AFFILIATION_REFERENCE_CODE
AND FT4.TERRITORY_CODE(+) = MMS.TRIANGULATION_COUNTRY_CODE

Columns

Name
ROW_ID
MOVEMENT_ID
ORGANIZATION_ID
MOVEMENT_STATUS
MOVEMENT_STATUS_MEANING
MOVEMENT_TYPE
MOVEMENT_TYPE_MEANING
DOCUMENT_SOURCE_TYPE
DOCUMENT_SOURCE_TYPE_MEANING
ENTITY_ORG_ID
LEGAL_ENTITY
TRANSACTION_DATE
BILL_TO_CUSTOMER_ID
CUSTOMER_BILL_TO
CUSTOMER_BILL_TO_NUMBER
BILL_TO_SITE_USE_ID
CUSTOMER_BILL_TO_SITE
INVOICE_BATCH_ID
BATCH_NAME
INVOICE_ID
INVOICE_NUM
INVOICE_DATE_REFERENCE
DOCUMENT_NUM
CUSTOMER_TRX_LINE_ID
INVOICE_LINE_NUM
INVOICE_QUANTITY
INVOICE_UNIT_PRICE
INVOICE_EXT_VALUE
FROM_ORGANIZATION_ID
FROM_ORG_CODE
FROM_ORG_NAME
TO_ORGANIZATION_ID
TO_ORG_CODE
TO_ORG_NAME
VENDOR_ID
VENDOR
VENDOR_NONMISC
VENDOR_NUM
VENDOR_NUM_NONMISC
VENDOR_SITE_ID
VENDOR_SITE
VENDOR_SITE_NONMISC
CUSTOMER
CUSTOMER_NONMISC
CUSTOMER_NUM
CUSTOMER_NUM_NONMISC
CUSTOMER_LOCATION
CUSTOMER_LOCATION_NONMISC
PO_HEADER_ID
PO_LINE_ID
PO_LINE_LOCATION_ID
ORDER_HEADER_ID
ORDER_LINE_ID
REQUISITION_HEADER_ID
REQUISITION_LINE_ID
PICKING_LINE_ID
PICKING_LINE_DETAIL_ID
SHIPMENT_HEADER_ID
SHIPMENT_LINE_ID
SHIP_TO_CUSTOMER_ID
SHIP_TO_SITE_USE_ID
DOCUMENT_REFERENCE
DOCUMENT_LINE_REFERENCE
SHIPMENT_REFERENCE
SHIPMENT_LINE_REFERENCE
PICK_SLIP_REFERENCE
RECEIPT_REFERENCE
TRANSACTING_FROM_ORG
TRANSACTING_TO_ORG
INVOICE_BATCH_REFERENCE
INVOICE_REFERENCE
INVOICE_LINE_REFERENCE
PARENT_MOVEMENT_ID
CURRENCY_CODE
INVENTORY_ITEM_ID
ITEM_DESCRIPTION
ITEM_COST
TRANSACTION_UOM_CODE
CATEGORY_ID
COMMODITY_CODE
COMMODITY_DESCRIPTION
TRANSACTION_QUANTITY
PRIMARY_QUANTITY
DOCUMENT_UNIT_PRICE
DOCUMENT_LINE_EXT_VALUE
TRANSACTION_NATURE
TRANSACTION_NATURE_MEANING
DELIVERY_TERMS
DELIVERY_TERMS_MEANING
TRANSPORT_MODE
TRANSPORT_MODE_MEANING
PORT
PORT_MEANING
AREA
AREA_MEANING
STAT_TYPE
MOVEMENT_NATURE_MEANING
WEIGHT_UOM_CODE
WEIGHT_METHOD
UNIT_WEIGHT
TOTAL_WEIGHT
STAT_METHOD
STAT_ADJ_PERCENT
STAT_ADJ_AMOUNT
STAT_EXT_VALUE
COMMENTS
ALTERNATE_QUANTITY
ALTERNATE_UOM_CODE
OUTSIDE_CODE
OUTSIDE_CODE_MEANING
DISPATCH_COUNTRY
DESTINATION_COUNTRY
ORIGIN_COUNTRY
DISPATCH_TERRITORY_CODE
DESTINATION_TERRITORY_CODE
ORIGIN_TERRITORY_CODE
OUTSIDE_UNIT_PRICE
OUTSIDE_EXT_VALUE
REPORT_REFERENCE
PERIOD_NAME
LAST_UPDATE_DATE
LAST_UPDATED_BY
CREATION_DATE
CREATED_BY
LAST_UPDATE_LOGIN
ATTRIBUTE_CATEGORY
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE5
ATTRIBUTE6
ATTRIBUTE7
ATTRIBUTE8
ATTRIBUTE9
ATTRIBUTE10
ATTRIBUTE11
ATTRIBUTE12
ATTRIBUTE13
ATTRIBUTE14
ATTRIBUTE15
ZONE_CODE
EDI_SENT_FLAG
MOVEMENT_NATURE_CODE
MOVEMENT_AMOUNT
TRIANGULATION_COUNTRY
TRIANGULATION_COUNTRY_CODE
CSA_CODE
CSA_MEANING
OIL_REFERENCE_CODE
OIL_REFERENCE_MEANING
CONTAINER_TYPE_CODE
CONTAINER_TYPE_MEANING
FLOW_INDICATOR_CODE
FLOW_INDICATOR_MEANING
AFFILIATION_REFERENCE_CODE
AFFILIATION_REFERENCE_MEANING
ORIGIN_TERRITORY_EU_CODE
DESTINATION_TERRITORY_EU_CODE
DISPATCH_TERRITORY_EU_CODE
SET_OF_BOOKS_PERIOD
EDI_TRANSACTION_DATE
EDI_TRANSACTION_REFERENCE
TARIC_CODE
PREFERENCE_CODE
TRIANGULATION_COUNTRY_EU_CODE
DISTRIBUTION_LINE_NUMBER
RCV_TRANSACTION_ID
MTL_TRANSACTION_ID
USAGE_TYPE
ZONE_NAME