Reports2017-11-18T12:27:27+00:00

INV Movement Statistics

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Movement Statistics Report (XML)
Short Name: INVSTMVT_XML
DB package: INV_INVSTMVT_XMLP_PKG
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(:CS_ALTERNATE_QUANTITY, :CS_TRANSACTION_QUANTITY) 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(:CS_STATISTICAL_VALUE) CF_STATISTICAL_VALUE_R, 
	INV_INVSTMVT_XMLP_PKG.cf_transaction_value_rformul(:CS_TRANSACTION_VALUE) 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(:CS_OUTSIDE_VALUE, :CF_PRECISION_TRX) CF_OUTSIDE_VALUE_R, 
	INV_INVSTMVT_XMLP_PKG.cf_document_line_ext_val_rform(:CS_DOC_VALUE, :CF_PRECISION_TRX) CF_DOCUMENT_LINE_EXT_VAL_R, 
	INV_INVSTMVT_XMLP_PKG.cf_invoice_line_ext_val_rformu(:CS_INVOICE_VALUE, :CF_PRECISION_TRX) CF_INVOICE_LINE_EXT_VAL_R,
&PLEX_ITEM_FLEX C_ITEM_FLEX
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')  
  &P_MVT_ID_WHERE 
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
Parameter Name SQL text Validation
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
INV_SRS_CHARACTER
 
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Amount Display Format
 
LOV Oracle
Report Option
 
LOV Oracle
Period Name
 
LOV Oracle
Movement Type
 
LOV Oracle
Statistical Type
 
LOV Oracle
Usage Type
 
LOV Oracle
Economic Zone
 
LOV Oracle
Legal Entity
 
LOV Oracle
By continuing to use the site, you agree to the use of cookies. Accept