oracleinventory bg xir2
TRANSCRIPT
-
8/10/2019 OracleInventory Bg XIR2
1/52
BusinessObjects Inventory Rapid Mart for
Oracle Applications Business Guide
Version 11.5.1.1
-
8/10/2019 OracleInventory Bg XIR2
2/52
Patents Business Objects owns the following U.S. patents, which may cover products that are offered
and sold by Business Objects: 5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and
6,289,352.
Trademarks Business Objects, the Business Objects logo, Crystal Reports, and Crystal Enterprise are
trademarks or registered trademarks of Business Objects SA or its affiliated companies in theUnited States and other countries. All other names mentioned herein may be trademarks of
their respective owners.
Copyright Copyright 2006 Business Objects. All rights reserved.
-
8/10/2019 OracleInventory Bg XIR2
3/52
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 3
Contents
Chapter 1 Introduction 5
What is a Rapid Mart? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Rapid Marts Accelerate Time to Value . . . . . . . . . . . . . . . . . . . . . . . . . 6BusinessObjects Rapid Mart architecture . . . . . . . . . . . . . . . . . . . . . . . 7
About this document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Chapter 2 Overview 9
What you can do with this Rapid Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Supported analyses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Related Rapid Marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Sharing components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Rapid Mart schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Chapter 3 Subject Areas 15
Material Transaction Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Oracle Applications processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Inventory Snapshot Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23Oracle Applications processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
-
8/10/2019 OracleInventory Bg XIR2
4/52
Contents
4 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Physical Inventory Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Oracle Applications processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Reservation Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Oracle Applications processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Chapter 4 Reports 35
Material Transaction Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Stock Issue and Receipt by Item Category . . . . . . . . . . . . . . . . . . 37
Subinventory Transfer Summary . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Inventory Snapshot Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Inventory Snapshot . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Physical Inventory Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Physical Inventory Approved Adjustments . . . . . . . . . . . . . . . . . . . 47
Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Reservation Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Current Reserved Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
-
8/10/2019 OracleInventory Bg XIR2
5/52
c h a p t e r
Introduction
-
8/10/2019 OracleInventory Bg XIR2
6/52
Introduction
What is a Rapid Mart?1
6 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
What is a Rapid Mart?
BusinessObjects Rapid Marts provide packaged extraction of operational
data to accelerate the deployment of business intelligence (BI). They are add-on products to BusinessObjects Data Integrator that combine domain
knowledge with data integration best practices to deliver pre-built data
models, transformation logic, and data flows for extracting data from
enterprise applications from SAP, PeopleSoft, Oracle, J. D. Edwards, and
Siebel.
Rapid Marts deliver pre-packaged business content and data flows in a single
data platform optimized for BI and analytic applications. Rapid Marts enable
business users to report on and gain insight into their enterprise information.
Most include prebuilt Business Objects reports and universes that deliver
domain knowledge on operational systems.
BI tools and analytic tools can access Rapid Mart data directly through SQL
queries or indirectly through a Data Integrator RealTime request-response
system. Rapid Marts can be implemented individually, or in any combination,
to form a single platform that delivers the infrastructure for your companys
internal and external information needs. They can also serve as a staging
area for enterprise analytic applications.
Rapid Marts provide IT with an accelerated time to value through rapid BI
deployments because you can implement them quickly and easily customize
them to meet specific analytic requirement.
Rapid Marts Accelerate Time to Value
Rapid Marts are packaged data solutions that you can quickly deploy to
address specific areas of business analysis.
Available Rapid Marts include:
BusinessObjects Sales Rapid Mart
BusinessObjects Cost Center Rapid Mart
BusinessObjects Profitability Rapid Mart
BusinessObjects General Ledger Rapid Mart
BusinessObjects Accounts Receivable Rapid Mart BusinessObjects Accounts Payable Rapid Mart
BusinessObjects Federal Financials Rapid Mart
BusinessObjects Fixed Assets Rapid Mart
BusinessObjects Purchasing Rapid Mart
BusinessObjects Inventory Rapid Mart
-
8/10/2019 OracleInventory Bg XIR2
7/52
Introduction
About this document1
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 7
BusinessObjects Plant Maintenance Rapid Mart
BusinessObjects HR Rapid Mart
BusinessObjects Production Planning Rapid Mart
BusinessObjects Project Systems Rapid Mart BusinessObjects Pipeline Rapid Mart
You can combine multiple Rapid Marts into a single environment to build the
foundation for your data warehouse or use them as a staging area for
BusinessObjects Analytic Applications.
BusinessObjects Rapid Mart architecture
Rapid Marts provide a powerful jumpstart for building source-specific data
marts for operational reporting and packaged data integration for enterprise
analytic applications. The following diagram shows where Rapid Marts fit inthe deployemt of business intelligence process:
About this document
This document describes the BusinessObjects Inventory Rapid Mart
for Oracle Applications. This document contains information for a variety ofusersinformation that helps you understand the use of the Rapid Mart, the
data in the Rapid Mart, the queries you can answer with the Rapid Mart, and
how to run, update, and maintain the Rapid Mart.
This document contains the following chapters:
-
8/10/2019 OracleInventory Bg XIR2
8/52
Introduction
About this document1
8 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Chapter 2: Overview Describes the business problems you can solveand the types of analyses you can do with this Rapid Mart
Chapter 3: Subject Areas Contains detailed information about eachsection that is part of the batch extraction in theRapid Mart, including the
processes each section captures
Chapter 4: Reports Provides examples of reports you can produce
-
8/10/2019 OracleInventory Bg XIR2
9/52
c h a p t e r
Overview
-
8/10/2019 OracleInventory Bg XIR2
10/52
Overview
Overview2
10 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Overview
This chapter describes the business problems you can solve with the
BusinessObjects Inventory Rapid Mart for Oracle Applications, and the typesof analyses you can do with this Rapid Mart. The information in this chapter is
useful for those who want a business-level overview of the Rapid Mart and its
benefits.
This chapter contains the following sections:
What you can do with this Rapid Mart
Supported analyses
Related Rapid Marts
Rapid Mart schema
What you can do with this Rapid Mart
The BusinessObjects Inventory Rapid Mart for Oracle Applications supports
analysis of data in the Inventory module of Oracle Applications. This module
provides the physical management of a companys stock that is available for
sale, distribution or internal consumption.
The BusinessObjects Inventory Rapid Mart for Oracle Applications provides a
framework for analysing stock levels for the purposes of planning and control
helping to establish an optimally sized buffer between finished goods supply
and demand. The end result is more efficient production planning andscheduling.
The BusinessObjects Inventory Rapid Mart for Oracle Applications is made
up of several componentized sections. Each section supports a subject
related to analysis of inventory management:
Material Transaction Section - tracks all transactions relating to items inthe inventory including receipt, transfer and issue of stock, adjustment of
stock levels and cost updates.
Inventory Snapshot Section - holds aggregated current and historicaldaily counts of on-hand quantities of stock.
Physical Inventory Section - maintains detailed records of physicalinventory counts including rejected counts. Approved adjustments arisingfrom these counts are posted as transactions and are included in the
Material Transaction Section.
-
8/10/2019 OracleInventory Bg XIR2
11/52
Overview
Supported analyses 2
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 11
Reservation Section - contains current and future requirements of itemsreserved for sales orders, accounts, account aliases, inventories or user-
defined sources. The current available quantity for items can be derived
by deducting the reserved quantity from the on-hand quantity stored in
the Inventory Snapshot Section.
Chapter 3: Subject Areasdiscusses the section and the data extracted in
more detail.
Supported analyses
The BusinessObjects Inventory Rapid Mart for Oracle Applications supports
several types of analyses, including :-
Business function Types of analysis Measures available in the Rapid Mart
Inventory managementand control
Trend analysis Current and historic daily inventoryby item, subinventory and inventory
organization
Stock turnover andoutages
Consumption compared to overallstock level by item, subinventory
and inventory organization
Statistical analysis Current period averages versusprior period inventory
Stock movement anddistribution analysis
Stock movements maintained at thestorage location level
Physical inventory Physical inventory versus systeminventory
Inventory valuation Stock valuation Daily updates of on-hand stockvaluation
Item requirementsplanning
Inventory levels Current and historic inventorycombined with past consumption
Expenditure forecast Current and historic material costs
Stock level optimization Stock outages and turnover versuscurrent safety stock level
Stock availabilityanalysis
Current stock levels subject toexisting material reservations
-
8/10/2019 OracleInventory Bg XIR2
12/52
Overview
Related Rapid Marts2
12 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Related Rapid Marts
Each Rapid Mart is composed of multiple components. A component is a
stand-alone workflow that completes a particular task, such as loading aspecific dimension table. Components can contain other components. A
section is a set of components that address a particular business problem or
subject area. A section is itself a component.
The BusinessObjects Inventory Rapid Mart for Oracle Applications shares a
few components as well as some transactional data with other Rapid Marts
including Purchasing, Sales and General Ledger Rapid Marts
Several dimensions are used by multiple Rapid Marts. For instance,items and subinventory components are part of Purchasing Rapid Mart.
Purchase Orders related to receipt of stock are included in the
Purchasing Rapid Mart. Sales orders relating to the issue of stock are included in the Sales Rapid
Mart.
Accounting entries for Inventory Transactions are included in the GeneralLedger Rapid Mart.
Sharing components
The same components can be used in multiple Rapid Marts. For example, a
component that extracts information about materials bought, produced, and
sold is needed for a Rapid Mart that supports sales analysis and also for aRapid Mart that supports inventory analysis. Work flows that extract star
schema dimensions are components. You can add a component to any
Rapid Mart using a simple import procedure.
A Data Integrator job can include multiple instances of a component. For
example, each section includes all the required dimension components.
Therefore, a job with several sections may include several instances of a
particular dimension component. Components are set to execute only once
within a job. This execute once feature ensures that shared components do
not cause duplicate data extraction from Oracle Applications. For more
information about the execute once feature, see the Data Integrator
Designer Guide.
Each of the sections listed in What you can do with this Rapid Mart on
page 10is considered a component. You can identify a component within a
Data Integrator job by a C_ prefix before its name. For example, the
component that contains inventory snapshot information and the associated
reporting dimensions is named C_InventorySnapshot_Section_OA.
-
8/10/2019 OracleInventory Bg XIR2
13/52
Overview
Rapid Mart schema 2
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 13
Rapid Mart schema
The following diagram shows the tables in the BusinessObjects Inventory
Rapid Mart for Oracle Applications. The larger boxes contain the fact tablescorresponding to componentized sections; the smaller boxes indicate
dimensional components that may apply to multiple sections.
-
8/10/2019 OracleInventory Bg XIR2
14/52
Overview
Rapid Mart schema2
14 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
-
8/10/2019 OracleInventory Bg XIR2
15/52
c h a p t e r
Subject Areas
-
8/10/2019 OracleInventory Bg XIR2
16/52
Subject Areas
Overview3
16 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Overview
Each section in the BusinessObjects Inventory Rapid Mart for Oracle
Applications pertains to a particular subject area. This chapter describes eachsection and the processes each section captures.
The information in this chapter is useful for readers who use the Rapid Mart
and need to understand the data in the Rapid Mart and how it relates to
Oracle Applications.
This chapter discusses:
Material Transaction Section
Inventory Snapshot Section
Physical Inventory Section
Reservation Section
Material Transaction Section
The Material Transaction section of this Rapid Mart stores data relating to
transactions of items in the inventory.
Oracle Applications processing
In Oracle Applications a hierachical storage scheme is defined as follows:-
Inventory organizations usually have different purposes (manufacturing,sales, etc) and different geographical locations.
Subinventories reflect different storage areas within an inventoryorganization and are usually defined by the physical location and the
nature of the items being stored.
Stock locators are the the actual addresses of storage locations withinthe subinventory. These are optional by subinventory. Flexfields are used
to define the stock locator and typically include segments for aisle, rack
and bin.
Oracle Applications provide a complete set of transactions for maintaining
inventory control from the time items are received into stock to the time thefinished goods are shipped to the customer. A transaction is an item
movement into, within, or out of inventory that changes the quantity, location
or inventory value of an item.
The main types of transactions are:-
Receipt into stores which may be related to a purcahse order
-
8/10/2019 OracleInventory Bg XIR2
17/52
Subject Areas
Material Transaction Section 3
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 17
Transfer of stock between subinventories and / or locators
Direct transfer between organizations
Issue from stores which may be related to a sales order
Adjustments related to physical inventories and cycle counts Cost updates
Oracle Applications holds details of all transactions in a single table,
MTL_MATERIAL_TRANSACTIONS.
Rapid Mart processing
The Rapid Mart selects all transactions from
MTL_MATERIAL_TRANSACTIONS source table and loads them into the
MATERIAL_TRANSACTION_FACT target table.
The extraction process is the same for both initial and incremental loads andthe last updated date field is used to filter the records. During incremental
loads, using the table comparison feature, transactions are first checked
against the target table to determine if they exist and have changed in which
case the record is updated in the target table. All new entries, as for the initial
load, are simply inserted into the target table.
-
8/10/2019 OracleInventory Bg XIR2
18/52
Subject Areas
Material Transaction Section3
18 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Rapid Mart data
The following diagram shows the tables that the Material Transaction section
extracts.
Fact table columns
The purchase requisition fact table contains transaction measures and
attributes. The columns in the table are described below.
TRANSACTION_ID
Transaction identifier, primary key
TRANSACTION_TYPE_ID
Transaction type identifier, foreign key to
MATERIAL_TRANSACTION_TYPE dimension
-
8/10/2019 OracleInventory Bg XIR2
19/52
Subject Areas
Material Transaction Section 3
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 19
TRANSACTION_SOURCE_TYPE_ID
Transaction source type identifier, foreign key to
MATERIAL_TXN_SOURCE_TYPE dimension
ORG_IDOrganization identifier, foreign key to ORGANIZATION dimension
SUBINVENTORY_CODE, ORG_ID
Transaction secondary inventory, foreign key to SUBINVENTORY
dimension
LOCATOR_ID, ORG_ID
Locator id for stock locators, foreign key to INVENTORY_LOCATION
dimension
INVENTORY_ITEM_ID, ORG_ID
Inventory item identifier, foreign key to ITEM dimension TRANSFER_ORG_ID
Transfer opposing organization id, foreign key to ORGANIZATION
dimension
TRANSFER_SUBINVENTORY_CODE, TRANSFER_ORG_ID
Transfer transaction opposing subinventory, foreign key to
SUBINVENTORY dimension
TRANSFER_LOCATOR_ID, TRANSFER_ORG_ID
Transfer transaction opposing locator id, foreign key to
INVENTORY_LOCATION dimension TRANSACTION_DATE
Transaction date, foreign key to TIME_DIM diemsion
PERIOD_SET_NAME,PERIOD_NAME
GL period name, foreign key to PERIOD_CALENDAR dimension
REASON_ID
Transaction reason identifier, foreign key to
MATERIAL_TRANSACTION_REASON dimension
CREATION_DATE
Standard When column
CREATED_BY_USER_ID
Standard Who column, foreign key to APPS_USER dimension
REVISION
Inventory item revision
-
8/10/2019 OracleInventory Bg XIR2
20/52
Subject Areas
Material Transaction Section3
20 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
COSTED_FLAG
Costed flag to mark if the transactions are costed or not
INVOICED_FLAG
Invoiced flag to mark if the transactions are invoiced
CURRENCY_CODE
Currency conversion code, foreign key to CURRENCY dimension
CURRENCY_CONVERSION_RATE
Currency conversion rate
CURRENCY_CONVERSION_DATE
Currency conversion date
TRANSACTION_QUANTITY
Transaction quantity
TRANSACTION_UOM_CODE
Transaction unit of measure, foreign key to UNIT_OF_MEASURE
dimension
PRIMARY_QUANTITY
Transaction quantity in terms of primary uom of the item
VARIANCE_AMOUNT
Used for average cost update transactions
ACTUAL_COST
Actual cost TRANSACTION_COST
The cost entered by the user during a transaction
PRIOR_COST
Item cost before the transaction
NEW_COST
Item cost after the transaction processing
TRANSFER_COST
Transfer cost specified for inter-org transfers
TRANSPORTATION_COST
Transfer transportation cost
VALUE_CHANGE
Total value change for average cost update
PERCENTAGE_CHANGE
-
8/10/2019 OracleInventory Bg XIR2
21/52
Subject Areas
Material Transaction Section 3
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 21
Percentage average cost change for average cost update
PRIOR_COSTED_QUANTITY
Costed quantity before this transaction
TRANSFER_PRIOR_COSTED_QUANTITYTransfer quantity costed before this transaction
TRANSFER_PERCENTAGE
Percentage of value to attribute to transfer costs in an Inter-Orgtransfer
DISTRIBUTION_ACCOUNT_ID
Distribution account identifier, foreign key to CODE_COMBINATION
dimension
ENCUMBRANCE_ACCOUNT_ID
Encumbrance account identifier for PO transactions, foreign key to
CODE_COMBINATION dimension
ENCUMBRANCE_AMOUNT
Total encumbrance amount
SHIPMENT_NUMBER
Intransit shipment identifier
PHYSICAL_ADJUSTMENT_ID
Physical adjustment identifier, foreign key to
PHYSICAL_ADJUSTMENT_FACT
RESERVATION_ID
Reservation identifier
LOAD_DATE
Date when the record was loaded into target system
LOAD_TIME
Time when the record was loaded
Analysis
With the tables in this section, you can analyze material transaction data
along several dimensions including:
By item
By subinventory
By organization
By date and period
-
8/10/2019 OracleInventory Bg XIR2
22/52
Subject Areas
Material Transaction Section3
22 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
By fixed segment dimensions (Company, Account, Cost Center, Project,Fund, Intercompany, Product, District) and associated hierarchies
You can combine the material transaction table,
MATERIAL_TRANSACTION_FACT, with other components in this Rapid
Mart:
Link transactions to inventory snapshot data
Link transactions to detailed physical inventory adjustments
Link transactions to current item reservations.
Typical queries include:
Compare the receipt and issue of items form a given subinventory
Track usage of raw materials
Analyse the cost of subinventory transfers
Show value of all goods for a particular item issued to scrap during anygiven period
-
8/10/2019 OracleInventory Bg XIR2
23/52
Subject Areas
Inventory Snapshot Section 3
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 23
Inventory Snapshot Section
The Inventory Snapshot section holds daily snapshots of on-hand inventory
stock levels.
Oracle Applications processing
Oracle Applications maintains the current on-hand quantity for each item by
stock locator and subinventory. Whenever a material transaction is posted the
quantities are adjusted accordingly. These quantities are held in the
MTL_ONHAND_QUANTITIES_DETAIL table.
Oracle Applications only maintains the current values, historical values are
not readily available.
Rapid Mart processing
Oracle Applications provides a number of views based on data in the
MTL_ONHAND_QUANTITIES_DETAIL table and the Rapid Mart uses one of
these views MTL_ONHAND_QTY_COST_V which includes the unit cost for
the item.
The Rapid Mart simply selects all data from this view and loads it into the
INVENTORY_SNAPSHOT_FACT target table.
The extraction process is the same for both initial and incremental loads.
However during incremental loads, any data already loaded that day is first
deleted from the target table to avoid duplication of data. Over time with
subsequent incremental loads, a historical daily snapshot is built up that
contains both the on-hand quantities and the unit cost of the item at that date.
-
8/10/2019 OracleInventory Bg XIR2
24/52
Subject Areas
Inventory Snapshot Section3
24 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Rapid Mart data
The following diagram shows the tables that the Inventory Snapshot section
extracts.
Fact table columns
The inventory snapshot fact table contains transaction measures and
attributes. The columns in the table are described below. SNAPSHOT_DATE
Date of Inventory Snapshot (the date data is loaded), foreign key to
TIME_DIM diemsion
ORG_ID
Organization identifier, foreign key to ORGANIZATION dimension
SUBINVENTORY_CODE, ORG_ID
Transaction secondary inventory, foreign key to SUBINVENTORY
dimension
LOCATOR_ID, ORG_IDLocator id for stock locators, foreign key to INVENTORY_LOCATION
dimension
INVENTORY_ITEM_ID, ORG_ID
Inventory item identifier, foreign key to ITEM dimension
DATE_RECEIVED
-
8/10/2019 OracleInventory Bg XIR2
25/52
Subject Areas
Inventory Snapshot Section 3
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 25
Date received
REVISION
Item revision code
LOT_NUMBERLot number
ITEM_COST
Unit cost of the item
TRANSACTION_QUANTITY
Transaction quantity
CREATE_TRANSACTION_ID
Transaction identifier of the transaction that created this row, foreign key
to MATERIAL_TRANSACTION_FACT table
UPDATE_TRANSACTION_ID
Transaction identifier of the transaction that last updated this row, foreign
key to MATERIAL_TRANSACTION_FACT table
LOAD_DATE
Date when the record was loaded into target system
LOAD_TIME
Time when the record was loaded
Analysis
With the tables in this section, you can analyze purchase order data along
several dimensions including:
By item
By subinventory
By organization
By date and period
-
8/10/2019 OracleInventory Bg XIR2
26/52
Subject Areas
Physical Inventory Section3
26 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
You can combine the inventory snapshot table,
INVENTORY_SNAPSHOT_FACT, with other components in this Rapid Mart:
Link on-hand quantitiy records to the material transactions fact table toobtain details of the related transactions.
Typical queries include:
Compare the value of the on-hand inventory over time.
What is the average reduction in inventory at this location since wealtered our replenishment strategy?
Physical Inventory Section
The Physical Inventory section of this Rapid Mart contains physical inventory
counts including any adjustments made.
Oracle Applications processing
Oracle Applications provides a fully automated physical inventory feature that
is used to reconcile system-maintained on-hand quantities with actual counts
of the inventory.
This involves firstly defining a physical inventory for all or selected
subinventories. Then a snapshot is taken of the current balances and using
this snapshot as a baseline, count tags are generated for the warehouse staff.
After counting has been completed, the counts can be accepted or rejected
based on tolerane levels and any approved adjustments are posted asmaterial transactions.
Details of the counts, including whether the count has been rejected or
approved and posted are held in the MTL_PHYSICAL_ADJUSTMENTS
table.
Rapid Mart processing
The Rapid Mart selects the physical inventory counts from
MTL_PHYSICAL_ADJUSTMENTS and loads them into the
PHYSICAL_ADJUSTMENTS_FACT target table.The extraction process is the same for both initial and incremental loads and
the last updated date field is used to filter the records. During incremental
loads, using the table comparison feature, physical inventory count records
are first checked against the target table to determine if they exist and have
changed in which case the record is updated in the target table. All new
entries, as for the initial load, are simply inserted into the target table.
-
8/10/2019 OracleInventory Bg XIR2
27/52
Subject Areas
Physical Inventory Section 3
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 27
Rapid Mart data
The following diagram shows the tables that the Physical Inventory section
extracts.
-
8/10/2019 OracleInventory Bg XIR2
28/52
Subject Areas
Physical Inventory Section3
28 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Fact table columns
The physical adjustment fact table contains transaction measures and
attributes. The columns in the table are described below.
ADJUSTMENT_IDPhysical inventory adjustment identifier, primary key
PHYSICAL_INVENTORY_ID
Identifier of physical inventory owning the adjustment, foreign key to
PHYSICAL_INVENTORY dimension
ORG_ID
Organization identifier, foreign key to ORGANIZATION dimension
SUBINVENTORY_CODE, ORG_ID
Transaction secondary inventory, foreign key to SUBINVENTORY
dimension
LOCATOR_ID, ORG_ID
Locator id for stock locators, foreign key to INVENTORY_LOCATION
dimension
INVENTORY_ITEM_ID, ORG_ID
Inventory item identifier, foreign key to ITEM dimension
REVISION
Revision of item being adjusted
LOT_NUMBER
Lot number of the item being adjusted
LOT_EXPIRATION_DATE
Expiration date of the lot number of this adjustment
SERIAL_NUMBER
Serial number of the item being adjusted
CREATION_DATE
Standard When column, foreign key to TIME_DIM diemsion
CREATED_BY_USER_ID
Standard Who column, foreign key to APPS_USER dimension
APPROVED_BY_PERSON_ID
Identifier of employee who approved or rejected this adjustment, foreign
key to CURRENT_PEOPLE dimension
APPROVED_FLAG
Indicates whether adjustment has been approved
-
8/10/2019 OracleInventory Bg XIR2
29/52
Subject Areas
Physical Inventory Section 3
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 29
POSTED_FLAG
Indicates whether adjustment has been posted
REJECTED_FLAG
Indicates whether adjustment has been rejected
COUNT_QUANTITY
Quantity of this SKU counted during the physical inventory
ADJUSTMENT_QUANTITY
Quantity adjustment required to correct frozen quantity to counted
quantity
SYSTEM_QUANTITY
Frozen quantity of this SKU
ACTUAL_COST
Cost per unit of this item
LOAD_DATE
Date when the record was loaded into target system
LOAD_TIME
Time when the record was loaded
Analysis
With the tables in this section, you can analyze physical inventory data along
several dimensions including:
By physical inventory
By item
By subinventory
By organization
By date and period
By approver
You can combine the physical inventory adjustment table,
PHYSICAL_ADJUSTMENT_FACT, with other components in this Rapid
Mart:
Link physical inventory adjustment records to the material transactionsfact table to obtain details of the posted transactions.
Typical queries include:
List all rejected adjustments
Show the value lost / gained from the adjustments
-
8/10/2019 OracleInventory Bg XIR2
30/52
Subject Areas
Reservation Section3
30 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Reservation Section
The Reservation section of this Rapid Mart contains current and future
requirements of reserved items.
Oracle Applications processing
Oracle Applications allows items to be reserved for sales orders, accounts,
account aliases, inventories or user-defined sources so that they cannot be
used for any other purpose. The on-hand quantities in the Inventory Snapshot
section include items that are reserved. To determine the available quantity,
the reserved items need to be subtracted from the on-hand quantity.
Details of item reservations are stored in the MTL_RESERVATIONS table
and this table only holds the currently reserved items. History of reservations
is not maintained. Once the reserved item is consumed or issued, the
reservation record is deleted.
Rapid Mart processing
The Rapid Mart selects item reservation records from MTL_RESERVATIONS
source table and loads them into the RESERVATION_FACT target table.
There is no incremental load, data is always deleted from the target table
prior to loading and the target table only ever contains details of currently
reserved items.
-
8/10/2019 OracleInventory Bg XIR2
31/52
Subject Areas
Reservation Section 3
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 31
Rapid Mart data
The following diagram shows the tables that the Reservation section extracts.
Fact table columns
The reservation fact table contains transaction measures and attributes. The
columns in the table are described below.
RESERVATION_ID
Reservation identifier, primary key
REQUIREMENT_DATE
Reservation requirement date
ORG_ID
Organization identifier, foreign key to ORGANIZATION dimension
SUBINVENTORY_CODE, ORG_ID
Transaction secondary inventory, foreign key to SUBINVENTORY
dimension
LOCATOR_ID, ORG_ID
Locator id for stock locators, foreign key to INVENTORY_LOCATION
dimension
INVENTORY_ITEM_ID, ORG_ID
Inventory item identifier, foreign key to ITEM dimension
REVISION
-
8/10/2019 OracleInventory Bg XIR2
32/52
Subject Areas
Reservation Section3
32 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Inventory item revision
LOT_NUMBER
Lot number of lot controlled reserved item
CREATION_DATEStandard When column
CREATED_BY_USER_ID
Standard Who column, foreign key to APPS_USER dimension
SHIP_READY_FLAG
Indicates that reserved quantity is ready to be shipped
STAGED_FLAG
Indicates if the reservation is in staged area.
PRIMARY_UOM_CODE
Primary unit of measure of reserved item, foreign key to
UNIT_OF_MEASURE dimension
RESERVATION_UOM_CODE
Unit of measure in which reservation is made, foreign key to
UNIT_OF_MEASURE dimension
RESERVATION_QUANTITY
Quanity reserved in RESERVATION_UOM_CODE
PRIMARY_RESERVATION_QUANTITY
Quantity reserved in PRIMARY_UOM_CODE DEMAND_SOURCE_TYPE_ID
Indicates demand source for which reservation is made, foreign key to
MATERIAL_TXN_SOURCE_TYPE dimension
DEMAND_SOURCE_NAME
Identifier for inventory or user defined demand
DEMAND_SOURCE_HEADER_ID
Header identifier for source of demand. For Sales Order:
OE_ORDER_HEADERS.HEADER_ID. For Account:
GL_CODE_COMBINATIONS.CODE_COMBINATION_ID. For AccountAlias: MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
DEMAND_SOURCE_LINE_ID
Line ID for demand source. For Sales Order:
OE_ORDER_LINES.LINE_ID
DEMAND_SOURCE_DELIVERY
-
8/10/2019 OracleInventory Bg XIR2
33/52
Subject Areas
Reservation Section 3
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 33
Delivery identifier for demand sources having one
SUPPLY_SOURCE_TYPE_ID
Indicates supply source of reservation, foreign key to
MATERIAL_TXN_SOURCE_TYPE dimension SUPPLY_SOURCE_HEADER_ID
Header identifier of supply source
SUPPLY_SOURCE_LINE_ID
Indicates line identifier of supply source
SUPPLY_SOURCE_LINE_DETAIL
Indicates line detail identifier of supply source
SUPPLY_SOURCE_NAME
Indicates name of supply source
LOAD_DATE
Date when the record was loaded into target system
LOAD_TIME
Time when the record was loaded
Analysis
With the tables in this section, you can analyze item reservation transactions
along several dimensions including:
By item
By subinventory
By organization
By date and period
You can combine the reservation table, RESERVATION_FACT, with other
components in this Rapid Mart:
Determine available quantities by deducting item reservations from theon-hand quantities in the inventory snapshot fact table.
Typical queries include:
List all reserved items for sales orders by item category.
Show the current available quantities of items.
-
8/10/2019 OracleInventory Bg XIR2
34/52
Subject Areas
Reservation Section3
34 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
-
8/10/2019 OracleInventory Bg XIR2
35/52
c h a p t e r
Reports
-
8/10/2019 OracleInventory Bg XIR2
36/52
Reports
Overview4
36 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Overview
The BusinessObjects Inventory Rapid Mart for Oracle Applications comes
with a sample universe. You can use the Rapid Mart to produce many kindsof reports. This chapter provides examples of reports built on top of the
sample universe. The information in this chapter is useful for those who
analyze and produce reports with the Rapid Mart data.
This chapter shows some sample reports and the recommended joins for
each section in the Rapid Mart:
Material Transaction Section
Inventory Snapshot Section
Physical Inventory Section
Reservation Section
Material Transaction Section
The Material Transaction section of this Rapid Mart stores data relating to
transactions of items in the inventory.
Sample reports
Using the material transaction information, you can complete numerous
operational analyses and generate different reports. For example, reports youcan generate include:
Stock Issue and Receipt by Item Category
Subinventory Transfer Summary
-
8/10/2019 OracleInventory Bg XIR2
37/52
Reports
Material Transaction Section 4
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 37
Stock Issue and Receipt by Item Category
This report displays the quanties of items issued and received from and into
stock by item category:
-
8/10/2019 OracleInventory Bg XIR2
38/52
Reports
Material Transaction Section4
38 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
You can generate the data for this report using SQL logic, such as the
following Oracle SQL statement:
SELECT DISTINCT
TIME_DIM.CALENDAR_DATE, ITEM_VIEW.CATEGORY_DESC, ITEM_VIEW.ITEM_NUMBER, ITEM_VIEW.ITEM_DESCRIPTION, SUBINVENTORY.SUBINVENTORY_CODE, sum(MATERIAL_TRANSACTION_FACT.PRIMARY_QUANTITY), sum(case
when MATERIAL_TRANSACTION_TYPE.TRANSACTION_ACTION_ID = 1then MATERIAL_TRANSACTION_FACT.PRIMARY_QUANTITYelse 0 end),
sum(casewhen MATERIAL_TRANSACTION_TYPE.TRANSACTION_ACTION_ID = 27then MATERIAL_TRANSACTION_FACT.PRIMARY_QUANTITYelse 0 end)
FROM MATERIAL_TRANSACTION_TYPE, MATERIAL_TRANSACTION_FACT, SUBINVENTORY, TIME_DIM, ITEM_VIEW
WHEREMATERIAL_TRANSACTION_TYPE.TRANSACTION_TYPE_ID
=MATERIAL_TRANSACTION_FACT.TRANSACTION_TYPE_ID
AND SUBINVENTORY.ORG_ID=MATERIAL_TRANSACTION_FACT.ORG_IDAND SUBINVENTORY.SUBINVENTORY_CODE=MATERIAL_TRANSACTION_FACT.SUBINVENTORY_CODE
AND MATERIAL_TRANSACTION_FACT.TRANSACTION_DATE=TIME_DIM.CALENDAR_DATE
AND MATERIAL_TRANSACTION_FACT.INVENTORY_ITEM_ID=ITEM_VIEW.INVENTORY_ITEM_ID
AND MATERIAL_TRANSACTION_FACT.ORG_ID=ITEM_VIEW.ORG_IDAND MATERIAL_TRANSACTION_TYPE.TRANSACTION_ACTION
In ('Issue from stores','Receipt into stores')AND TIME_DIM.MONTH_YEAR
=@prompt('Calendar Monthand Year Equal to:','A','Calendar Period\Calendar Month and Year',
Mono,Constrained,Persistent,,User:0)
GROUP BY TIME_DIM.CALENDAR_DATE,ITEM_VIEW.CATEGORY_DESC,ITEM_VIEW.ITEM_NUMBER,ITEM_VIEW.ITEM_DESCRIPTION,SUBINVENTORY.SUBINVENTORY_CODE
-
8/10/2019 OracleInventory Bg XIR2
39/52
Reports
Material Transaction Section 4
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 39
Subinventory Transfer Summary
This report displays a summary of subinventory transfers grouped by the
source subinventory:
-
8/10/2019 OracleInventory Bg XIR2
40/52
Reports
Material Transaction Section4
40 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
You can generate the data for this report using SQL logic, such as the
following Oracle SQL statement:
SELECT DISTINCTTIME_DIM.CALENDAR_DATE, SUBINVENTORY.SUBINVENTORY_CODE, TRANSFER_SUBINVENTORY.SUBINVENTORY_CODE, ITEM_VIEW.CATEGORY_DESC, ITEM_VIEW.ITEM_NUMBER, sum(MATERIAL_TRANSACTION_FACT.PRIMARY_QUANTITY)
FROM MATERIAL_TRANSACTION_TYPE, MATERIAL_TRANSACTION_FACT, SUBINVENTORY, TIME_DIM,
SUBINVENTORY TRANSFER_SUBINVENTORY, ITEM_VIEW
WHEREMATERIAL_TRANSACTION_TYPE.TRANSACTION_TYPE_ID
=MATERIAL_TRANSACTION_FACT.TRANSACTION_TYPE_IDAND SUBINVENTORY.ORG_ID=MATERIAL_TRANSACTION_FACT.ORG_IDAND SUBINVENTORY.SUBINVENTORY_CODE
=MATERIAL_TRANSACTION_FACT.SUBINVENTORY_CODEAND MATERIAL_TRANSACTION_FACT.TRANSACTION_DATE
=TIME_DIM.CALENDAR_DATEAND TRANSFER_SUBINVENTORY.ORG_ID
=MATERIAL_TRANSACTION_FACT.TRANSFER_ORG_IDAND TRANSFER_SUBINVENTORY.SUBINVENTORY_CODE
=MATERIAL_TRANSACTION_FACT.TRANSFER_SUBINVENTORY_CODEAND MATERIAL_TRANSACTION_FACT.INVENTORY_ITEM_ID
=ITEM_VIEW.INVENTORY_ITEM_IDAND MATERIAL_TRANSACTION_FACT.ORG_ID=ITEM_VIEW.ORG_IDAND MATERIAL_TRANSACTION_TYPE.TRANSACTION_ACTION
= 'Subinventory transfer'AND MATERIAL_TRANSACTION_FACT.PRIMARY_QUANTITY > 0
GROUP BY TIME_DIM.CALENDAR_DATE,SUBINVENTORY.SUBINVENTORY_CODE,
TRANSFER_SUBINVENTORY.SUBINVENTORY_CODE,ITEM_VIEW.CATEGORY_DESC,ITEM_VIEW.ITEM_NUMBER
-
8/10/2019 OracleInventory Bg XIR2
41/52
Reports
Material Transaction Section 4
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 41
Recommended table joins
When analyzing data from the material transaction section, you need to
create joins between fact and dimension tables. Specifically, you need to
create joins between the MATERIAL_TRANSACTION_FACT table and other
dimension tables in the Rapid Mart.
Fact table column name Dimension table Column name
TRANSACTION_DATE TIME_DIM CALENDAR_DATE
CREATED_BY_USER_ID APPS_USER USER_ID
TRANSACTION_SOURCE_
TYPE_ID
MATERIAL_TXN_SOURCE_TYPE
TRANSACTION_SOURCE_
TYPE_ID
TRANSACTION_TYPE_ID MATERIAL_TRANSACTION
_TYPE
TRANSACTION_TYPE_ID
ORG_ID ORGANIZATION ORG_ID
ORG_ID SUBINVENTORY ORG_ID
SUBINVENTORY_CODE SUBINVENTORY SUBINVENTORY_CODE
ORG_ID INVENTORY_LOCATION ORG_ID
LOCATOR_ID INVENTORY_LOCATION LOCATOR_ID
ORG_ID ITEM_VIEW ORG_ID
INVENTORY_ITEM_ID ITEM_VIEW INVENTORY_ITEM_ID
TRANSFER_ORG_ID SUBINVENTORY ORG_ID
TRANSFER_SUBINVENTORY_CODE
SUBINVENTORY SUBINVENTORY_CODE
TRANSFER_ORG_ID INVENTORY_LOCATION ORG_ID
TRANSFER_LOCATOR_ID INVENTORY_LOCATION LOCATOR_ID
TRANSACTION_UOM_CODE UNIT_OF_MEASURE UOM_CODE
CURRENCY_CODE CURRENCY CURRENCY_CODE
REASON_ID MATERIAL_TRANSACTION_REASON
REASON_ID
PERIOD_NAME PERIOD_CALENDAR PERIOD_SET_NAMEPERIOD_NAME PERIOD_CALENDAR PERIOD_SET_NAME
DISTRIBUTION_ACCOUNT_ID CODE_COMBINATION CODE_COMBINATION_ID
-
8/10/2019 OracleInventory Bg XIR2
42/52
Reports
Material Transaction Section4
42 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
The material transaction fact table can also be joined with account segment
dimensions through CODE_COMBINATION
Column name Dimension table Column name
ACCOUNT_CODE COA_ACCOUNT ACCOUNT_CODE
COMPANY_CODE COA_COMPANY COMPANY_CODE
COST_CENTER_CODE COA_COST_CENTER COST_CENTER_CODE
PROJECT_CODE COA_PROJECT PROJECT_CODE
INTERCOMPANY_CODE COA_INTERCOMPANY INTERCOMPANY_CODE
FUND_CODE COA_FUND FUND_CODE
DISTRICT_CODE COA_DISTRICT DISTRICT_CODE
PRODUCT_CODE COA_PRODUCT PRODUCT_CODE
ACCOUNT_TYPE_CODE ACCOUNT_TYPE ACCOUNT_TYPE_CODE
-
8/10/2019 OracleInventory Bg XIR2
43/52
Reports
Inventory Snapshot Section 4
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 43
Inventory Snapshot Section
The Inventory Snapshot section holds daily snapshots of on-hand inventory
stock levels.
Sample reports
Using the daily inventory snapshots, you can complete numerous operational
analyses and generate different reports. For example, reports you can
generate include:
Inventory Snapshot
-
8/10/2019 OracleInventory Bg XIR2
44/52
Reports
Inventory Snapshot Section4
44 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Inventory Snapshot
This report displays a snapshot of the inventory for the given date:
-
8/10/2019 OracleInventory Bg XIR2
45/52
Reports
Inventory Snapshot Section 4
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 45
You can generate the data for this report using SQL logic, such as the
following Oracle SQL statement:
SELECT DISTINCT
TIME_DIM.CALENDAR_DATE, ITEM_VIEW.CATEGORY_DESC, ITEM_VIEW.ITEM_NUMBER, ITEM_VIEW.ITEM_DESCRIPTION, SUBINVENTORY.SUBINVENTORY_CODE, sum(INVENTORY_SNAPSHOT_FACT.TRANSACTION_QUANTITY), sum(INVENTORY_SNAPSHOT_FACT.ITEM_COST
* INVENTORY_SNAPSHOT_FACT.TRANSACTION_QUANTITY), max(floor(INVENTORY_SNAPSHOT_FACT.SNAPSHOT_DATE
- INVENTORY_SNAPSHOT_FACT.DATE_RECEIVED)), INVENTORY_LOCATION_VIEW.DESCRIPTION, INVENTORY_LOCATION_VIEW.ROW_FLEXK, INVENTORY_LOCATION_VIEW.RACK_FLEXK, INVENTORY_LOCATION_VIEW.BIN_FLEXK
FROM INVENTORY_LOCATION_VIEW, INVENTORY_SNAPSHOT_FACT, SUBINVENTORY, TIME_DIM, ITEM_VIEW
WHEREINVENTORY_LOCATION_VIEW.LOCATOR_ID
=INVENTORY_SNAPSHOT_FACT.LOCATOR_ID
AND INVENTORY_LOCATION_VIEW.ORG_ID=INVENTORY_SNAPSHOT_FACT.ORG_IDAND INVENTORY_SNAPSHOT_FACT.ORG_ID=SUBINVENTORY.ORG_IDAND INVENTORY_SNAPSHOT_FACT.SUBINVENTORY_CODE
=SUBINVENTORY.SUBINVENTORY_CODEAND TIME_DIM.CALENDAR_DATE=INVENTORY_SNAPSHOT_FACT.SNAPSHOT_DATEAND INVENTORY_SNAPSHOT_FACT.INVENTORY_ITEM_ID
=ITEM_VIEW.INVENTORY_ITEM_IDAND INVENTORY_SNAPSHOT_FACT.ORG_ID=ITEM_VIEW.ORG_IDAND TIME_DIM.CALENDAR_DATE
= @prompt('Calendar Day Equal to:','D',,Mono,Free,Persistent,,User:0)
GROUP BY TIME_DIM.CALENDAR_DATE,ITEM_VIEW.CATEGORY_DESC,ITEM_VIEW.ITEM_NUMBER,ITEM_VIEW.ITEM_DESCRIPTION,SUBINVENTORY.SUBINVENTORY_CODE,INVENTORY_LOCATION_VIEW.DESCRIPTION,INVENTORY_LOCATION_VIEW.ROW_FLEXK,
-
8/10/2019 OracleInventory Bg XIR2
46/52
Reports
Physical Inventory Section4
46 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
INVENTORY_LOCATION_VIEW.RACK_FLEXK,INVENTORY_LOCATION_VIEW.BIN_FLEXK
Recommended table joinsWhen analyzing data from the inventory snapshot section, you need to create
joins between fact and dimension tables. Specifically, you need to create
joins between the INVENTORY_SNAPSHOT_FACT table and other
dimension tables in the Rapid Mart.
Physical Inventory SectionThe Physical Inventory section of this Rapid Mart contains physical inventory
counts including any adjustments made.
Sample reports
Using the physical inventory information, you can complete numerous
operational analyses and generate different reports. For example, reports you
can generate include:
Physical Inventory Approved Adjustments
Fact table column name Dimension table Column name
SNAPSHOT_DATE TIME_DIM CALENDAR_DATE
ORG_ID ORGANIZATION ORG_ID
ORG_ID SUBINVENTORY ORG_ID
SUBINVENTORY_CODE SUBINVENTORY SUBINVENTORY_CODE
ORG_ID INVENTORY_LOCATION ORG_ID
LOCATOR_ID INVENTORY_LOCATION LOCATOR_ID
ORG_ID ITEM_VIEW ORG_ID
INVENTORY_ITEM_ID ITEM_VIEW INVENTORY_ITEM_ID
-
8/10/2019 OracleInventory Bg XIR2
47/52
Reports
Physical Inventory Section 4
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 47
Physical Inventory Approved Adjustments
This report displays the approved adjustments grouped by physical inventory:
-
8/10/2019 OracleInventory Bg XIR2
48/52
Reports
Physical Inventory Section4
48 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
You can generate the data for this report using SQL logic, such as the
following Oracle SQL statement:
SELECT DISTINCT
PHYSICAL_INVENTORY.PHYSICAL_INVENTORY_NAME, PHYSICAL_INVENTORY.PHYSICAL_INVENTORY_DATE, SUBINVENTORY.SUBINVENTORY_CODE, ITEM_VIEW.ITEM_NUMBER, CURRENT_PEOPLE.FULL_NAME, PHYSICAL_ADJUSTMENT_FACT.POSTED_FLAG, sum(PHYSICAL_ADJUSTMENT_FACT.ADJUSTMENT_QUANTITY), sum(PHYSICAL_ADJUSTMENT_FACT.ADJUSTMENT_QUANTITY
* PHYSICAL_ADJUSTMENT_FACT.ACTUAL_COST), sum(PHYSICAL_ADJUSTMENT_FACT.COUNT_QUANTITY), sum(PHYSICAL_ADJUSTMENT_FACT.SYSTEM_QUANTITY)
FROM SUBINVENTORY, PHYSICAL_ADJUSTMENT_FACT, CURRENT_PEOPLE, PHYSICAL_INVENTORY, ITEM_VIEW
WHERESUBINVENTORY.ORG_ID=PHYSICAL_ADJUSTMENT_FACT.ORG_ID
AND SUBINVENTORY.SUBINVENTORY_CODE=PHYSICAL_ADJUSTMENT_FACT.SUBINVENTORY_CODE
AND PHYSICAL_ADJUSTMENT_FACT.APPROVED_BY_PERSON_ID=CURRENT_PEOPLE.PERSON_ID
AND PHYSICAL_ADJUSTMENT_FACT.ORG_ID=PHYSICAL_INVENTORY.ORG_IDAND PHYSICAL_ADJUSTMENT_FACT.PHYSICAL_INVENTORY_ID=PHYSICAL_INVENTORY.PHYSICAL_INVENTORY_ID
AND PHYSICAL_ADJUSTMENT_FACT.INVENTORY_ITEM_ID=ITEM_VIEW.INVENTORY_ITEM_ID
AND PHYSICAL_ADJUSTMENT_FACT.ORG_ID=ITEM_VIEW.ORG_IDAND PHYSICAL_ADJUSTMENT_FACT.APPROVED_FLAG = 'Y'
GROUP BY PHYSICAL_INVENTORY.PHYSICAL_INVENTORY_NAME,PHYSICAL_INVENTORY.PHYSICAL_INVENTORY_DATE,SUBINVENTORY.SUBINVENTORY_CODE,ITEM_VIEW.ITEM_NUMBER,
CURRENT_PEOPLE.FULL_NAME,PHYSICAL_ADJUSTMENT_FACT.POSTED_FLAG
-
8/10/2019 OracleInventory Bg XIR2
49/52
Reports
Reservation Section 4
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 49
Recommended table joins
When analyzing data from the physical inventory section, you need to create
joins between fact and dimension tables. Specifically, you need to create
joins between the PHYSICAL_ADJUSTMENT_FACT table and other
dimension tables in the Rapid Mart.
Reservation SectionThe Reservation section of this Rapid Mart contains current and future
requirements of reserved items.
Sample reports
Using the reservation information, you can complete numerous operational
analyses and generate different reports. For example, reports you can
generate include:
Current Reserved Items
Fact table column name Dimension table Column name
CREATION_DATE TIME_DIM CALENDAR_DATE
CREATED_BY_USER_ID APPS_USER USER_ID
PHYSICAL_INVENTORY_ID PHYSICAL_INVENTORY PHYSICAL_INVENTORY_ID
ORG_ID ORGANIZATION ORG_ID
ORG_ID SUBINVENTORY ORG_ID
SUBINVENTORY_CODE SUBINVENTORY SUBINVENTORY_CODE
ORG_ID INVENTORY_LOCATION ORG_ID
LOCATOR_ID INVENTORY_LOCATION LOCATOR_ID
ORG_ID ITEM_VIEW ORG_ID
INVENTORY_ITEM_ID ITEM_VIEW INVENTORY_ITEM_ID
APPROVED_BY_PERSON_ID CURRENT_PEOPLE PERSON_ID
-
8/10/2019 OracleInventory Bg XIR2
50/52
Reports
Reservation Section4
50 BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide
Current Reserved Items
This report displays surrently reserved items grouped by item category:
-
8/10/2019 OracleInventory Bg XIR2
51/52
Reports
Reservation Section 4
BusinessObjects Inventory Rapid Mart for Oracle Applications Business Guide 51
You can generate the data for this report using SQL logic, such as the
following Oracle SQL statement:
SELECT DISTINCT
ITEM_VIEW.CATEGORY_DESC, ITEM_VIEW.ITEM_NUMBER, ITEM_VIEW.ITEM_DESCRIPTION, SUBINVENTORY.SUBINVENTORY_CODE, sum(RESERVATION_FACT.PRIMARY_RESERVATION_QUANTITY), RESERVATION_FACT.REQUIREMENT_DATE, UNIT_OF_MEASURE.UOM_NAME
FROM SUBINVENTORY, RESERVATION_FACT, UNIT_OF_MEASURE, ITEM_VIEW
WHERESUBINVENTORY.ORG_ID=RESERVATION_FACT.ORG_ID
AND SUBINVENTORY.SUBINVENTORY_CODE=RESERVATION_FACT.SUBINVENTORY_CODE
AND RESERVATION_FACT.PRIMARY_UOM_CODE=UNIT_OF_MEASURE.UOM_CODEAND RESERVATION_FACT.INVENTORY_ITEM_ID
=ITEM_VIEW.INVENTORY_ITEM_IDAND RESERVATION_FACT.ORG_ID=ITEM_VIEW.ORG_ID
GROUP BY ITEM_VIEW.CATEGORY_DESC,
ITEM_VIEW.ITEM_NUMBER,ITEM_VIEW.ITEM_DESCRIPTION,SUBINVENTORY.SUBINVENTORY_CODE,RESERVATION_FACT.REQUIREMENT_DATE,UNIT_OF_MEASURE.UOM_NAME
-
8/10/2019 OracleInventory Bg XIR2
52/52
Reports
Reservation Section4
Recommended table joins
When analyzing data from the reservation section, you need to create joins
between fact and dimension tables. Specifically, you need to create joins
between the RESERVATION_FACT table and other dimension tables in the
Rapid Mart.
Fact table column name Dimension table Column name
CREATION_DATE TIME_DIM CALENDAR_DATE
CREATED_BY_USER_ID APPS_USER USER_ID
SUPPLY_SOURCE_TYPE_ID MATERIAL_TXN_SOURCE_TYPE
TRANSACTION_SOURCE_
TYPE_ID
DEMAND_SOURCE_TYPE_ID MATERIAL_TXN_SOURCE_
TYPE
TRANSACTION_SOURCE_
TYPE_ID
ORG_ID ORGANIZATION ORG_ID
ORG_ID SUBINVENTORY ORG_ID
SUBINVENTORY_CODE SUBINVENTORY SUBINVENTORY_CODE
ORG_ID INVENTORY_LOCATION ORG_ID
LOCATOR_ID INVENTORY_LOCATION LOCATOR_ID
ORG_ID ITEM_VIEW ORG_ID
INVENTORY_ITEM_ID ITEM_VIEW INVENTORY_ITEM_ID
PRIMARY_UOM_CODE UNIT_OF_MEASURE UOM_CODE