View: INVFV_MOVEMENT_STATISTICS

View Text

SELECT MMS.MOVEMENT_ID
, MMS.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HOU.NAME
, '_LA:MMS.MOVEMENT_STATUS:FND_LOOKUPS:MVT_STATUSES:MEANING'
, /* LOOKUP */ '_LA:MMS.MOVEMENT_TYPE:FND_LOOKUPS:MVT_MOVEMENT_TYPE:MEANING'
, /* LOOKUP */ '_LA:MMS.DOCUMENT_SOURCE_TYPE:FND_LOOKUPS:MVT_SOURCE_DOCUMENT_TYPES:MEANING'
, /* LOOKUP */ MMS.ENTITY_ORG_ID
, HLE.NAME
, MMS.TRANSACTION_DATE
, /* BILL TO CUSTOMER */ MMS.BILL_TO_CUSTOMER_ID
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.BILL_TO_NAME
, RC1.CUSTOMER_NAME)
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.BILL_TO_NUMBER
, RC1.CUSTOMER_NUMBER)
, MMS.BILL_TO_SITE_USE_ID
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.BILL_TO_SITE
, RSU1.LOCATION)
, /* INVOICE */ MMS.INVOICE_BATCH_ID
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_BATCH_REFERENCE
, AB.BATCH_NAME)
, MMS.INVOICE_ID
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_REFERENCE
, 'SO'
, RCT.TRX_NUMBER
, AI.INVOICE_NUM)
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_DATE_REFERENCE
, 'SO'
, RCT.TRX_DATE
, AI.INVOICE_DATE)
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, NULL
, 'SO'
, RCT.DOC_SEQUENCE_ID
, AI.DOC_SEQUENCE_ID)
, MMS.CUSTOMER_TRX_LINE_ID
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_LINE_REFERENCE
, RCTL.LINE_NUMBER)
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_QUANTITY
, NVL(MMS.INVOICE_QUANTITY
, RCTL.QUANTITY_INVOICED))
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_UNIT_PRICE
, NVL(MMS.INVOICE_UNIT_PRICE
, RCTL.UNIT_SELLING_PRICE))
, DECODE(MMS.DOCUMENT_SOURCE_TYPE
, 'MISC'
, MMS.INVOICE_LINE_EXT_VALUE
, NVL(MMS.INVOICE_LINE_EXT_VALUE
, RCTL.EXTENDED_AMOUNT))
, /* ORGANIZATIONS */ MMS.FROM_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HOU1.NAME
, MMS.TO_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE
, HOU2.NAME
, /* VENDOR */ MMS.VENDOR_ID
, MMS.VENDOR_NAME
, PV.VENDOR_NAME
, MMS.VENDOR_NUMBER
, PV.SEGMENT1
, MMS.VENDOR_SITE_ID
, MMS.VENDOR_SITE
, PVS.VENDOR_SITE_CODE
, /* CUSTOMER */ MMS.CUSTOMER_NAME
, RC2.CUSTOMER_NAME
, MMS.CUSTOMER_NUMBER
, RC2.CUSTOMER_NUMBER
, MMS.CUSTOMER_LOCATION
, RSU2.LOCATION
, /* PO */ MMS.PO_HEADER_ID
, POH.SEGMENT1
, MMS.PO_LINE_ID
, POL.LINE_NUM
, MMS.PO_LINE_LOCATION_ID
, POLL.SHIPMENT_NUM
, /* SO */ MMS.ORDER_HEADER_ID
, SOH.ORDER_NUMBER
, MMS.ORDER_LINE_ID
, SOL.LINE_NUMBER
, /* REQ */ MMS.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, MMS.REQUISITION_LINE_ID
, PRL.LINE_NUM
, /* OTHERS */ 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
, '_KF:INV:MSTK:MSI'
, /* KEY FLEX */ MMS.ITEM_DESCRIPTION
, MMS.ITEM_COST
, MMS.TRANSACTION_UOM_CODE
, MMS.CATEGORY_ID
, '_KF:INV:MCAT:MC'
, /* KEY FLEX */ MMS.COMMODITY_CODE
, MMS.COMMODITY_DESCRIPTION
, MMS.TRANSACTION_QUANTITY
, MMS.PRIMARY_QUANTITY
, MMS.DOCUMENT_UNIT_PRICE
, MMS.DOCUMENT_LINE_EXT_VALUE
, '_LA:MMS.TRANSACTION_NATURE:FND_LOOKUPS:MVT_TRANSACTION_NATURE:MEANING'
, /* LOOKUP */ '_LA:MMS.DELIVERY_TERMS:FND_LOOKUPS:MVT_DELIVERY_TERMS:MEANING'
, /* LOOKUP */ '_LA:MMS.TRANSPORT_MODE:FND_LOOKUPS:MVT_TRANSPORT_MODE:MEANING'
, /* LOOKUP */ '_LA:MMS.PORT:FND_LOOKUPS:MVT_PORT:MEANING'
, /* LOOKUP */ '_LA:MMS.AREA:FND_LOOKUPS:MVT_AREA:MEANING'
, /* LOOKUP */ '_LA:MMS.STAT_TYPE:FND_LOOKUPS:MVT_STATISTICAL_TYPE:MEANING'
, /* LOOKUP */ 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
, '_LA:MMS.OUTSIDE_CODE:FND_LOOKUPS:MVT_OUTSIDE_CODE:MEANING'
, /* LOOKUP */ FT1.TERRITORY_SHORT_NAME
, FT2.TERRITORY_SHORT_NAME
, FT3.TERRITORY_SHORT_NAME
, 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
FROM HR_LEGAL_ENTITIES HLE
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS HOU1
, HR_ALL_ORGANIZATION_UNITS HOU2
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL POLL
, SO_HEADERS_ALL SOH
, SO_LINES_ALL SOL
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, MTL_SYSTEM_ITEMS MSI
, MTL_CATEGORIES MC
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, RA_CUSTOMERS RC1
, RA_SITE_USES_ALL RSU1
, AP_BATCHES_ALL AB
, AP_INVOICES_ALL AI
, RA_CUSTOMER_TRX_ALL RCT
, RA_CUSTOMER_TRX_LINES_ALL RCTL
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, RA_CUSTOMERS RC2
, RA_SITE_USES_ALL RSU2
, FND_TERRITORIES_TL FT1
, FND_TERRITORIES_TL FT2
, FND_TERRITORIES_TL FT3
, MTL_MOVEMENT_PARAMETERS MMP
, MTL_MOVEMENT_STATISTICS MMS
WHERE HLE.ORGANIZATION_ID = MMS.ENTITY_ORG_ID
AND MP.ORGANIZATION_ID = MMS.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MMS.ORGANIZATION_ID
AND MMP.ENTITY_ORG_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 FT1.TERRITORY_CODE (+) = MMS.DISPATCH_TERRITORY_CODE
AND USERENV('LANG') = FT1.LANGUAGE (+)
AND FT2.TERRITORY_CODE (+) = MMS.DESTINATION_TERRITORY_CODE
AND USERENV('LANG') = FT2.LANGUAGE (+)
AND FT3.TERRITORY_CODE (+) = MMS.ORIGIN_TERRITORY_CODE
AND USERENV('LANG') = FT3.LANGUAGE (+)
AND MSI.INVENTORY_ITEM_ID (+) = MMS.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID (+) = MMS.ORGANIZATION_ID
AND MC.CATEGORY_ID (+) = MMS.CATEGORY_ID
AND POH.PO_HEADER_ID (+) = MMS.PO_HEADER_ID
AND POL.PO_LINE_ID (+) = MMS.PO_LINE_ID
AND POLL.LINE_LOCATION_ID (+) = MMS.PO_LINE_LOCATION_ID
AND SOH.HEADER_ID (+) = MMS.ORDER_HEADER_ID
AND SOL.LINE_ID (+) = MMS.ORDER_LINE_ID
AND PRH.REQUISITION_HEADER_ID (+) = MMS.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID (+) = MMS.REQUISITION_LINE_ID
AND '_SEC:MMS.ORGANIZATION_ID' IS NOT NULL WITH READ ONLY

Columns