INV Movement Statistics

SELECT
    mms.parent_movement_id C_PARENT_MOVEMENT_ID,
    mms.invoice_id C_INVOICE_ID,
    mms.movement_id C_MOVEMENT_ID,
    mms.transaction_nature C_TRANSACTION_NATURE,
    mms.delivery_terms C_DELIVERY_TERMS,
    mms.dispatch_territory_eu_code || '-' || mms.dispatch_territory_code C_DISPATCH_TERR_EU_CODE,
    mms.destination_territory_eu_code || '-' || mms.destination_territory_code C_DESTINATION_TERR_EU_CODE,
    mms.origin_territory_eu_code || '-' || mms.origin_territory_code C_ORIGIN_TERR_EU_CODE,
    mms.area C_AREA,
    mms.port C_PORT,
    mms.stat_type C_STAT_TYPE,
    mms.inventory_item_id C_INVENTORY_ITEM_ID,
    mms.document_source_type C_DOCUMENT_SOURCE_TYPE,
    round(
        (mms.item_cost * :P_EXCHANGE_RATE),
        :cp_ext_precision_func
    ) C_ITEM_COST,
    mms.item_description C_ITEM_DESCRIPTION,
    mms.document_reference C_DOCUMENT_REFERENCE,
    mms.document_line_reference C_DOCUMENT_LINE_REFERENCE,
    mms.invoice_batch_reference C_INVOICE_BATCH_REFERENCE,
    mms.invoice_reference C_INVOICE_REFERENCE,
    mms.invoice_line_reference C_INVOICE_LINE_REFERENCE,
    mms.transaction_date C_TRANSACTION_DATE,
    mms.transaction_uom_code C_TRANSACTION_UOM_CODE,
    round(
        (mms.currency_conversion_rate * :P_EXCHANGE_RATE),
        5
    ) C_CURRENCY_CONVERSION_RATE,
    mms.currency_code C_MOVEMENT_CURRENCY_CODE,
    mms.outside_code C_OUTSIDE_CODE,
    mms.report_reference C_REPORT_REFERENCE,
    mms.transport_mode C_TRANSPORT_MODE,
    mms.customer_name C_CUSTOMER_NAME,
    mms.transacting_from_org C_TRANSACTING_FROM_ORG,
    mms.transacting_to_org C_TRANSACTING_TO_ORG,
    mms.vendor_name C_VENDOR_NAME,
    mms.ship_to_customer_id C_SHIP_TO_CUSTOMER_ID,
    mms.ship_to_site_use_id C_SHIP_TO_SITE_USE_ID,
    mms.vendor_id C_VENDOR_ID,
    mms.vendor_site_id C_VENDOR_SITE_ID,
    mms.from_organization_id C_FROM_ORGANIZATION_ID,
    mms.to_organization_id C_TO_ORGANIZATION_ID,
    mms.customer_number C_CUSTOMER_NUMBER,
    mms.vendor_number C_VENDOR_NUMBER,
    mms.customer_location C_CUSTOMER_LOCATION,
    mms.vendor_site C_VENDOR_SITE,
    mms.po_header_id C_PO_HEADER_ID,
    mms.order_header_id C_ORDER_HEADER_ID,
    mms.po_line_id C_PO_LINE_ID,
    mms.order_line_id C_ORDER_LINE_ID,
    mms.invoice_batch_id C_INVOICE_BATCH_ID,
    mms.alternate_uom_code C_ALTERNATE_UOM_CODE,
    mms.movement_status C_MOVEMENT_STATUS,
    mms.shipment_reference C_SHIPMENT_REFERENCE,
    mms.po_line_location_id C_PO_LINE_LOCATION_ID,
    mms.shipment_line_reference C_SHIPMENT_LINE_REFERENCE,
    mms.shipment_line_id C_SHIPMENT_LINE_ID,
    mms.receipt_reference C_RECEIPT_REFERENCE,
    mms.pick_slip_reference C_PICK_SLIP_REFERENCE,
    mms.picking_line_id C_PICKING_LINE_ID,
    mms.invoice_date_reference C_INVOICE_DATE_REFERENCE,
    mms.shipment_header_id C_SHIPMENT_HEADER_ID,
    mms.comments C_COMMENTS,
    mms.container_type_code C_CONTAINER,
    mms.statistical_procedure_code C_STATISTICAL_PROCEDURE,
    mms.customer_trx_line_id C_CUSTOMER_TRX_LINE_ID,
    mms.movement_type C_MOVEMENT_TYPE,
    mms.requisition_header_id C_REQUISITION_HEADER_ID,
    mms.requisition_line_id C_REQUISITION_LINE_ID,
    mms.mtl_transaction_id C_MTL_TRANSACTION_ID,
    mms.distribution_line_number C_DISTRIBUTION_LINE_NUMBER,
    mms.rcv_transaction_id C_RCV_TRANSACTION_ID,
    mms.commodity_code C_COMMODITY_CODE,
    mms.commodity_description C_COMMODITY_DESCRIPTION,
    msi.primary_uom_code C_PRIMARY_UOM_CODE,
    mstl.description C_ITEM_DESC,
    fl.meaning C_MEANING,
    INV_INVSTMVT_XMLP_PKG.cf_release_numberformula(mms.rcv_transaction_id) CF_RELEASE_NUMBER,
    INV_INVSTMVT_XMLP_PKG.cf_parent_mvt_terr_codeformula(
        mms.dispatch_territory_eu_code || '-' || mms.dispatch_territory_code,
        mms.destination_territory_eu_code || '-' || mms.destination_territory_code
    ) CF_PARENT_MVT_TERR_CODE,
    INV_INVSTMVT_XMLP_PKG.cf_commodity_fieldformula() CF_COMMODITY_FIELD,
    INV_INVSTMVT_XMLP_PKG.cf_parent_mvt_quantityformula(
        sum(alternate_quantity) over (partition by parent_movement_id),
        sum(transaction_quantity) over (partition by parent_movement_id)
    ) CF_PARENT_MVT_QUANTITY,
    INV_INVSTMVT_XMLP_PKG.cf_precision_trxformula(mms.currency_code) CF_PRECISION_TRX,
    INV_INVSTMVT_XMLP_PKG.cf_parent_mvt_uomformula(mms.alternate_uom_code, mms.transaction_uom_code) CF_PARENT_MVT_UOM,
    INV_INVSTMVT_XMLP_PKG.cf_statistical_value_rformul(
        (
            sum(stat_ext_value) over (partition by parent_movement_id)
        ) * :P_EXCHANGE_RATE
    ) CF_STATISTICAL_VALUE_R,
    INV_INVSTMVT_XMLP_PKG.cf_transaction_value_rformul(
        (
            sum(movement_amount) over (partition by parent_movement_id)
        ) * :P_EXCHANGE_RATE
    ) CF_TRANSACTION_VALUE_R,
    INV_INVSTMVT_XMLP_PKG.cf_invoiceformula(
        mms.document_source_type,
        mms.invoice_reference,
        mms.invoice_id,
        mms.movement_type
    ) CF_INVOICE,
    INV_INVSTMVT_XMLP_PKG.cf_invoice_batchformula(
        mms.document_source_type,
        mms.invoice_batch_reference,
        mms.invoice_batch_id
    ) CF_INVOICE_BATCH,
    INV_INVSTMVT_XMLP_PKG.cf_invoice_lineformula(
        mms.document_source_type,
        mms.invoice_line_reference,
        mms.movement_type,
        mms.distribution_line_number,
        mms.customer_trx_line_id
    ) CF_INVOICE_LINE,
    INV_INVSTMVT_XMLP_PKG.cf_invoice_uom_codeformula(
        mms.customer_trx_line_id,
        mms.transaction_uom_code
    ) CF_INVOICE_UOM_CODE,
    fnd_flex_xml_publisher_apis.process_kff_combination_1(
        'CF_ITEM_FIELD',
        'INV',
        'MSTK',
        101,
        MSI.ORGANIZATION_ID,
        MSI.INVENTORY_ITEM_ID,
        'ALL',
        'Y',
        'VALUE'
    ) CF_ITEM_FIELD,
    INV_INVSTMVT_XMLP_PKG.cf_shipment_lineformula(
        mms.document_source_type,
        mms.shipment_line_reference,
        mms.shipment_line_id
    ) CF_SHIPMENT_LINE,
    INV_INVSTMVT_XMLP_PKG.cf_receipt_numberformula(
        mms.document_source_type,
        mms.receipt_reference,
        mms.pick_slip_reference,
        mms.shipment_header_id,
        mms.shipment_reference
    ) CF_RECEIPT_NUMBER,
    INV_INVSTMVT_XMLP_PKG.cf_shipment_numberformula(
        mms.document_source_type,
        mms.shipment_reference,
        mms.po_line_location_id
    ) CF_SHIPMENT_NUMBER,
    INV_INVSTMVT_XMLP_PKG.cf_source_line_numberformula(
        mms.document_source_type,
        mms.document_line_reference,
        mms.po_line_id,
        mms.order_line_id,
        mms.movement_type,
        mms.requisition_line_id
    ) CF_SOURCE_LINE_NUMBER,
    INV_INVSTMVT_XMLP_PKG.cf_source_numberformula(
        mms.document_source_type,
        mms.document_reference,
        mms.po_header_id,
        mms.order_header_id,
        mms.movement_type,
        mms.requisition_header_id,
        mms.mtl_transaction_id
    ) CF_SOURCE_NUMBER,
    INV_INVSTMVT_XMLP_PKG.cf_trader_numberformula(
        mms.document_source_type,
        mms.customer_number,
        mms.ship_to_customer_id,
        mms.vendor_number,
        mms.transacting_from_org,
        mms.transacting_to_org
    ) CF_TRADER_NUMBER,
    INV_INVSTMVT_XMLP_PKG.cf_trader_siteformula(
        mms.document_source_type,
        mms.customer_location,
        mms.ship_to_site_use_id,
        mms.vendor_site,
        mms.customer_name,
        mms.vendor_name,
        mms.transacting_from_org,
        mms.transacting_to_org
    ) CF_TRADER_SITE,
    INV_INVSTMVT_XMLP_PKG.cf_trader_typeformula(
        mms.document_source_type,
        mms.customer_name,
        mms.vendor_name,
        mms.transacting_from_org,
        mms.transacting_to_org
    ) CF_TRADER_TYPE,
    INV_INVSTMVT_XMLP_PKG.cf_trader_nameformula(
        mms.document_source_type,
        mms.customer_name,
        mms.ship_to_customer_id,
        mms.vendor_name,
        mms.from_organization_id,
        mms.to_organization_id,
        mms.transacting_from_org,
        mms.transacting_to_org
    ) CF_TRADER_NAME,
    INV_INVSTMVT_XMLP_PKG.cf_outside_value_rformula(
        sum(outside_ext_value) over (partition by parent_movement_id),
        INV_INVSTMVT_XMLP_PKG.cf_precision_trxformula(mms.currency_code)
    ) CF_OUTSIDE_VALUE_R,
    INV_INVSTMVT_XMLP_PKG.cf_document_line_ext_val_rform(
        sum(document_line_ext_value) over (partition by parent_movement_id),
        INV_INVSTMVT_XMLP_PKG.cf_precision_trxformula(mms.currency_code)
    ) CF_DOCUMENT_LINE_EXT_VAL_R,
    INV_INVSTMVT_XMLP_PKG.cf_invoice_line_ext_val_rformu(
        sum(invoice_line_ext_value) over (partition by parent_movement_id),
        INV_INVSTMVT_XMLP_PKG.cf_precision_trxformula(mms.currency_code)
    ) CF_INVOICE_LINE_EXT_VAL_R,
    (msi.concatenated_segments) C_ITEM_FLEX,
    sum(transaction_quantity) over(partition by mms.parent_movement_id) C_TRANSACTION_QUANTITY,
    sum(alternate_quantity) over(partition by mms.parent_movement_id) C_ALTERNATE_QUANTITY,
    sum(primary_quantity) over(partition by mms.parent_movement_id) C_PRIMARY_QUANTITY,
    sum(invoice_quantity) over(partition by mms.parent_movement_id) C_INVOICE_QUANTITY,
    sum(total_weight) over(partition by mms.parent_movement_id) C_TOTAL_WEIGHT,
    sum(invoice_line_ext_value) over(partition by mms.parent_movement_id) C_INVOICE_LINE_EXT_VALUE,
    sum(document_line_ext_value) over(partition by mms.parent_movement_id) C_DOCUMENT_LINE_EXT_VALUE,
    sum(outside_ext_value) over(partition by mms.parent_movement_id) C_OUTSIDE_EXT_VALUE,
    (
        sum(movement_amount) over(partition by mms.parent_movement_id)
    ) * :P_EXCHANGE_RATE C_TRANSACTION_VALUE,
    (
        sum(stat_ext_value) over(partition by mms.parent_movement_id)
    ) * :P_EXCHANGE_RATE C_STATISTICAL_VALUE
FROM
    mtl_movement_statistics mms,
    mtl_system_items_kfv msi,
    fnd_lookups fl,
    mtl_system_items_tl mstl --Bugfix 18839247
WHERE
    mms.inventory_item_id = msi.inventory_item_id(+)
    and mms.organization_id = msi.organization_id(+)
    and msi.inventory_item_id = mstl.inventory_item_id
    and msi.organization_id = mstl.organization_id
    and mstl.language = USERENV('LANG')
    and fl.lookup_type = 'MVT_SOURCE_DOCUMENT_TYPES'
    and fl.lookup_code = mms.document_source_type
    and mms.movement_type = :P_MOVEMENT_TYPE
    and mms.entity_org_id = :P_LEGAL_ENTITY_ID
    and mms.zone_code = :P_ZONE_CODE
    and upper(mms.usage_type) = upper(:P_USAGE_TYPE)
    and upper(mms.stat_type) = upper(:P_STAT_TYPE)
    and mms.period_name = :P_PERIOD_NAME
    and (
        mms.movement_status = 'O'
        or mms.movement_status = 'V'
    )
    and mms.parent_movement_id = mms.movement_id
ORDER BY
    C_COMMODITY_CODE,
    C_PARENT_MOVEMENT_ID,
    C_MOVEMENT_ID,
    C_TRANSPORT_MODE,
    C_TRANSACTION_NATURE,
    C_DISPATCH_TERR_EU_CODE,
    C_DESTINATION_TERR_EU_CODE