Product: | INV - Inventory |
---|---|
Description: | |
Implementation/DBA Data: | Not implemented in this database |
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