Understanding the Supply Chain Warehouse Structure

Data marts are logical divisions within the SCM Warehouse and are comprised of subject-specific dimensional data models designed around a specific institutional process.

Image: Supply Chain Warehouse marts and business processes

The SCM Warehouse includes the Fulfillment and Billing data mart, Procurement data mart, Spend data mart, Inventory data mart, Manufacturing data mart, and Supply Chain Planning data mart.

Each mart is associated with a business process that helps you answer the questions that you need to keep your organization robust and ahead of its competition. With each mart, we present the fact tables that will help you answer your critical business questions.

Note: Although we list only the actual fact name, all facts that represent dollar amounts have three facts in the fact table, one for each possible selected reporting currency. Populating the facts for alternate currencies is optional.

For additional details of facts, see the PDF file that is published on CD-ROM with your documentation.

This section discusses:

Fulfillment and Billing Mart
Procurement Mart
Spend Mart
Inventory Mart
Manufacturing Mart
Supply Chain Planning Mart

Fulfillment and Billing Data Mart

Analytics derived from Fulfillment and Billing data mart support the order-to-cash processes within the supply chain by providing analytics that track customers, orders, invoices, returns, and shipping transactions. You can use the analysis of individual product sales by channel, region, and other dimensions to greatly assist future marketing and product development initiatives, yielding a significant bottom line impact. Analytics cannot only help maintain customer satisfaction, but they can also help drive efficiencies into the supply chain, maintain cost controls, and ensure that fulfillment is aligned with manufacturing capabilities. The data mart also enables complete margin analysis, helping you to identify and track sales, returns, costs, discounts, and gross profit by customer.

With Fulfillment and Billing data mart you can answer questions such as:

Which customers are returning the most products?
Which products are being returned the most often?
Why are products being returned?
Which customers contribute the most to profit?
Which products produce the most profit?
Which products are discounted the most, and to which customers?
What is the overall order fill rate?
What is the fill rate for each product?
What is the percentage of late shipments?
What is the value of the late shipments?
What are the total dollars shipped per period?
What was the total value of shipped products for a given period?
What was the total cost of the shipped products?
What is the gross margin per product shipped?

Order Fulfillment Business Process

Fulfillment and Billing data mart is related to the Order Fulfillment business process, which is also known as Order to Cash. The Order Fulfillment business process fulfills an organization's requirements for capturing, fulfilling, and settling goods sold. With the Order Fulfillment business process, you capture, confirm, and manage sales orders and contracts, deliver goods or services, and then invoice, collect, and resolve payment. The Order Fulfillment business process also helps you to manage returns and inventory, process customer payments, and maintain profitable customer relationships.

Fulfillment and Billing Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Fulfillment and Billing data mart.

Fulfillment and Billing Data Mart Fact Tables

The following table describes the delivered Fulfillment and Billing data mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	Description	Helps Answer
Billing	F_BILLING	The Billing fact table contains billing transaction information. This is an incremental fact table.	Revenue by customer. Quantity charged on an invoice. Discount charged on an invoice. Tax amount on an invoice. Invoice gross amount. Invoice net amount.
Booking	F_BOOKINGS	Stores information related to sales booking and enables you to evaluate an employee based on the sales they generate.	Number of sales orders, booked amount, and discount by product, sales person and by requested ship date. Sales order net booked quantity. Sales order net booked amount. Set booked cost for a sales order. Net booked shipped quantity for a sales order. Backordered quantity for a sales order.
Sales Kit Item	F_KIT_ITEM	Stores information related to sales kit items.	Unit cost of each sales order line item. Number of kit items, the item unit cost, and shipped quantities associated with a product, a sales order, and a business unit. Unit price of each sales order line item. Ordered kit item quantity. Shipped kit item quantity. Cancelled kit item quantity.
Sales Order Cycle Time	F_O2C_LTCT	Stores information related to sales order cycle time and lead time and provides a view of the complete order to cash process so you can evaluate the time it takes to complete the process − or life cycle − of Order to Cash. The order to cash cycle time is a key measure of cash flow efficiency. The facts enable you identify each segment of the cycle to understand which segments need improvement.	Cycle time and lead time for sales order booking to shipping. Shipping to delivery. Delivery to return Order to invoice Order to cash. Lead time in days from sales order to order pick. Lead time in days from order schedule to order shipment. Lead time in days from order date to actual ship date. Lead time in days from order date to delivery date. Lead time in days from delivery date to return date. Lead time in days from sales order to invoice. Lead time in days from sales order to cash. Difference between quoted lead time and actual shipment lead time.
Return Material Authorization (RMA)	F_RMA_RECEIVED	Stores information about returned material from a sales order and aids in evaluating return sales order price, quantity, and so on. This fact group helps you analyze customer returns by product, channel, carrier, and shipment date.	Number of products returned. Returned amount by customer, by product, by channel, by carrier. Number of sales order line items returned. Return price for the sales order line item. Total sales order quantity returned. Total sales order return amount.
Sales Order	F_SALES_ORDER	Stores information related to the sales order header level and helps evaluate metrics such as Net Price, Quantity, List Amount, and so on.	Product sales by channel, by customers, by shipment dates, or by business unit. Price ordered by a customer. Billback value associated with a sales order. List price of the product that was ordered. Total dollars ordered by product or customer. Sales order extended price.
Sales Order Line	F_SO_LINE	Stores information related to sales order line items, such as order quantities and amounts, at a sales order line level of detail.	Unit cost and unit price for each item Number of units ordered for each item. Lead time for an ordered item and number shipped. Number of backordered units. Number of units that are cancelled.
Sales Order Shipment	F_SO_SHIP	Stores information related to sales order line and shipping line.	Shipment dates and delivery performance by customer, by channel, by product, or by carrier. Number of items shipped for a sales order. Total shipping cost for an invoice. Number of line items for a sales order. Shipping price for a specific line item. Unit price of each sales order line item. Quantity shipped for each sales order line item. Quantity backordered for each sales order line item. Extended cost for the sales order line item.

Fulfillment and Billing Data Mart Dimension Tables

The following table describes the delivered Fulfillment and Billing data mart dimension tables.

Dimension Name	Dimension Record Name	Description
Back Order Status	D_BCKORD_STATUS	Stores back order statuses.
Fulfilment Status	D_FULFLMNT_STAT	Stores fulfillment status.
Last Status	D_LAST_STAT	Stores last type.
Order Type	D_ORD_TYP	Stores supply chain order types.
Reason Code	D_REASON_CODE	Stores reason codes, such as inventory reason code, rejection reason codes, and so on.
RMA Status	D_RMA_STATUS	Stores return material authorization statuses.
RMA Cancel	D_RMACNL_REASON	Stores return material cancel reason statuses.
RMA Line	D_RMALN_STATUS	Stores return material line statuses.
Sales Organization	D_SALES_ORG	Stores sales organization and its hierarchy.
Sales Order Type	D_SLSORD_TYPE	Stores sales order types.
Sales Quote Type	D_SLSQUOTE_TYPE	Stores sales quote type.
Sales Order Status	D_SO_STATUS	Stores sales order statuses.
SO Line Status	D_SOLN_STATUS	Stores sales order line statuses.

Procurement Data Mart

With the Procurement data mart, you evaluate the reliability, timeliness, and efficiency of your procurement process and supply base. Analyzing buyer workload, requisition fulfillment and purchase order processing enables you to identify and eliminate key procurement process inefficiencies. Procurement data mart also enables you to evaluate detailed supplier receipt, such as on time deliveries, over or under shipments, and product quality for complete supplier performance analysis. This crucial view of your procurement process can help your company expose problems early, reducing cost and increasing customer satisfaction.

Procurement data mart enables you to answer questions such as:

What is the total quantity ordered under contract per supplier or buyer?
What is the total dollar amount committed under contract per supplier or buyer?
What is the total dollar amount committed (ordered) per supplier?
Which suppliers contribute the most in terms of purchase order activity?
Which buyers are the most productive?
Which suppliers have the most returns?
How much improvement has a supplier made in returns?
How much more purchasing volume has transpired over the year?
What is the total amount ordered from a specific supplier over a specified time period?
What is the total purchase order dollars controlled by one buyer?
Has the number of rejected units from a supplier improved over time? Is a specific item causing the greatest number of rejects?
What is the supplier service quality?
How often does a supplier under or over ship?
Which suppliers have the best acceptance rates?
What percentage of time does a supplier ship early or late?
Do the early or late shipments occur at the beginning or end of the month?
What is the average difference between the shipment due date and receipt date? Has the supplier improved over time?
How often does a supplier over or under ship orders?
What is the value of those under or over shipments?
Is a supplier improving shipment accuracy from period to period?
What is the acceptance rate for orders received?
Has the percent inspected increased due to supplier past acceptance rates?
What is the value of the rejected orders?
How long does a requisition take to process?
What is the amount requisitioned by each department?

Procurement Business Process

Procurement Mart is tied to PeopleSoft's Procurement business process, which is also known as the Source to Settle business process. The Procurement business process fulfills an organization's requirements for sourcing, engaging, procuring, and settling payment for goods, services, or both. The Procurement business process enables you to determine profitability and sourcing strategies, collaborate with suppliers, and drive efficient procurement and settlement for all goods and services.

Procurement Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Procurement data mart.

Procurement Data Mart Fact Tables

The following table describes the delivered Procurement data mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	Description	Helps Answer
Match Analysis	F_MTCH_ANLYS	Stores the voucher dollar amount and age of vouchers related to invoice-entered date, invoice last updated date, and invoice age. This information is stored at the Voucher Line level.	Age of an invoice. Lead-time for each matching process by supplier, item, date, or business unit. Amount for a voucher. Length of time between invoice entered date and invoice matching date.
PO Disposition	F_PO_DISP	Stores information on purchase order disposition, such as units disposed. This helps in maintaining an accurate unit count for a particular item.	Number of disposed units in the primary unit of measure.
PO Distribution	F_PO_DIST	Stores purchase order distribution information such as Merchandise Amount and Ordered Quantity. This information is stored at the Purchase Order Distribution Line level.	PO freight amount for a supplier. PO merchant amount for a carrier. PO sales tax amount for a business unit or item. Number of lines on the purchase order that have shipped.
PO Line	F_PO_LINE	Stores information on purchase order line, such as Unit Cost of a Commodity, Units On Hold, Units Ordered, on Hold Amount, Open Amount, Relieved Amount. This information is stored at purchase order line level.	PO unit price by supplier. Total number of purchase order units ordered. Total received amount. Number of units on hold. Total number of units received. Total amount deducted from inventory to fill an order.
PO Line Matching	F_PO_LN_MACT	Stores information on purchase order line match.	-
PO Receipt	F_PO_RCPT	Stores information on purchase order receipts and vouchers. It captures the information of receipt record and voucher record with details such as open quantity and open amount. All measures are at the inventory item level of detail.	PO open quantity and open amount for a business unit and for a corresponding account. PO receipt adjusted quantity. PO receipt closed amount.
PO Receipt Matching	F_PO_RCPT_MACT	Stores information on purchase order receipt match.	-
PO Shipment Receipt	F_PO_SHIP_RCPT	Stores information on purchase order receipts for the received line of Shipments. This information is stored at the Receipt Line Shipment level.	Receipt Amount for a line of shipment from a particular location or a particular receipt date or for a given due date. PO accepted amount for a particular shipment. PO accepted quantity for a particular shipment. PO rejected quantity for a particular shipment.
Procurement Cycle Time	F_PROC_CTLT	Stores procurement cycle time lead time analysis measures. This information is stored at the Purchase Order Line level.	Lead time for a procurement process by supplier, business unit, date, or by reject reason. Lead time quote by a supplier. Number of days from the purchase order dispatch to the material receipt. Number of days from material receipt to the voucher.
Requisition	F_REQN	Stores requisition line level measures.	Quantity for a material requisition. Price for a material requisition. Buyer and buyer's department for a material requisition.
Requisition Analysis	F_REQN_ANLYSIS	Stores requisition analysis measures. This information is stored at the Requisition Line level.	Lead time for a requisition process by business unit, buyer, buyer department, or location. Time taken from purchase order requisition to approval. Time taken from purchase order dispatch to material receipt. Time taken from the voucher to the last payment.
Return to Vendor	F_RTV	Stores return to vendor line level facts.	Price of the units returned to vendor for a particular supplier. Number of units were returned to vendor for a specific item. Number of returned items on a purchase order.
Return to Vendor Distribution	F_RTV_DIST	Stores return to vendor shipping line level information, such as PO Amount Returned and PO Quantity Returned. This information is stored at the RTV Shipping Line level.	Number of returned items for a business unit. Return reason. Number of items returned for a particular date. Price of the returned item. Total amount for returned items on a purchase order.

Procurement Data Mart Dimension Tables

The following table describes the delivered Procurement data mart dimension tables.

Dimension Name	Dimension Record Name	Description
Cancel Status	D_CNCL_STATUS	Stores supply chain cancel statuses.
Completion Status	D_COMP_STATUS	Stores completion statuses regarding supply chain.
Delivery Status	D_DLVRY_STATUS	Stores delivery statuses regarding supply chain.
Item Cost	D_ITEM_COST	Specifies the basis for calculating item costs.
Match Status	D_MTCH_STATUS	Stores purchase order match statuses.
Origin	D_ORIGIN	Stores origin codes, such as purchase order origin codes.
PO Status	D_PO_STATUS	Stores purchase order status.
PO Distribution Status	D_PODIST_STATUS	Stores purchase order distribution status.
Receive Status	D_RECV_STATUS	Stores receive statuses regarding supply chain.
Requisition Status	D_REQ_STATUS	Stores purchase order requisition statuses.
Requisition Line Status	D_REQLN_STATUS	Stores requisition line statuses.
Return Reason Status	D_RTRN_REASON	Stores return reason statuses regarding supply chain.
Return to Vendor Status	D_RTV_STATUS	Stores return to vendor statuses.
Return to Vendor line Status	D_RTVLN_STATUS	Stores return to vendor line statuses.
Shipment Status	D_SHIP_STATUS	Stores ship statuses.
Ship To	D_SHIPTO	Stores ship to locations.

Spend Data Mart

Every supply chain manager has an implicit goal to drive down unit cost without sacrificing product and service quality. But most managers do not have enough visibility into their supply chain to effectively manage the cost. The Spend data mart enables you to examine the source-to-settle process to isolate maverick spending, excessive purchase price variances, and contract compliance issues. With the Spend data mart, you are able to evaluate your supplier diversity programs to increase your government subsidies, look for opportunities to consolidate your supply base, and maximize your contract negotiations by leveraging supplier performance statistics. You will also gain visibility into spending across divisions and access, analyze and uncover information about spending patterns to help enhance relationships with existing suppliers, and initiate more profitable relationships with new suppliers. With Spend Mart you will be able to answer questions such as:

With which suppliers do we spend the most
On which supplies do we spend the most
What departments have the most maverick spend
With which minority owned businesses are we spending the most
Why are invoices not being paid on time
Which supplier and products have the largest purchase price variance

Spend Business Process

The Spend data mart is related to PeopleSoft's Procurement business process, which is also known as the Source to Settle business process. This business process is described with the Procurement data mart. The Spend data mart helps measure the dollars flowing through the Source to Settle process. This data mart enables you to analyze potential spending, for example, requisition amounts or open purchase order amounts, or invoiced amounts from suppliers, actual payments to suppliers, net discounts and adjustments.

Spend Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Spend data mart.

Spend Data Mart Fact Tables

The following table describes the delivered Spend data mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	Description	Helps Answer
Voucher	F_VCHR	Stores information relating to a voucher at the header level, such as payment and discount amounts. All measures are at the voucher level of detail.	Amount paid out against a voucher and associated discounts by business unit or by date. Number of payments made against a voucher. Amount of discount associated with payment for a voucher.
Voucher Line	F_VCHR_LN	Stores information relating to a voucher at the line level, such as voucher amounts, price variances, and amounts paid to date. All measures are at the voucher line level of detail.	PO price compared to the voucher price by business unit, buyer, or by date. PO price associated with a voucher line item. Voucher price associated with the voucher line item.

Fact Name

Fact Record Name

Description

Helps Answer

Voucher

F_VCHR

Stores information relating to a voucher at the header level, such as payment and discount amounts. All measures are at the voucher level of detail.

Amount paid out against a voucher and associated discounts by business unit or by date.

Number of payments made against a voucher.

Amount of discount associated with payment for a voucher.

Voucher Line

F_VCHR_LN

Stores information relating to a voucher at the line level, such as voucher amounts, price variances, and amounts paid to date. All measures are at the voucher line level of detail.

PO price compared to the voucher price by business unit, buyer, or by date.

PO price associated with a voucher line item.

Voucher price associated with the voucher line item.

Spend Data Mart Dimension Tables

The following table describes the delivered Spend data mart dimension tables.

Dimension Name	Dimension Record Name	Description
Commodity Code	D_CMMDTY_CD	Standard code used throughout business industries that assists in categorizing or grouping procurement items.
Payment Status	D_PYMT_STATUS	Stores voucher payment statuses.
United Nations Standard Product and Service Codes	D_UNSPSC_CODE	Stores the standard product and service codes developed by Dun & Bradstreet for use in electronic commerce for data sourced from PeopleSoft.

Inventory Data Mart

Inventory management is an act of balancing cost and service. Maintaining large inventories is expensive and risky. But, small inventories may compromise your ability to satisfy a sudden increase in demand. The Inventory data mart provides supply chain performance analytics centered around these key inventory issues, such as demand, ability to meet demand, inventory turns, inbound supplies, quantities on hand, and other key metrics. These analytics can enable you to limit the direct costs of maintaining excess inventory, as well as the direct and indirect costs of not meeting the services levels required by your customers.

The Inventory data mart enables you to answer questions such as:

How accurate is my inventory application count versus physical count
Which inventory items have the best or worst accuracy
cost of the inaccuracy
value of inventory on hand
Number of weeks of supply are on hand
demand by item and by location
inventory value of the demand
Has the weeks of supply improved from last year
How much lower or higher are the inventory turns from last year
Has the inventory accuracy improved from last year
Is there enough supply to meet demand
status of the on hand stock
Which items, and Number of, are on hold
What are the fast and slow movers (items)
What are the fast and slow mover (item) trends
value of the fast and slow movers
How often does inventory turn by item or by inventory location
inventory value by item or by location
Number of units of an item are currently in inventory
Number of weeks of supply are available to meet current demand
Number of units are on order to help meet demand

Inventory Business Process

The Inventory data mart supports multiple business process, including Order Fulfillment, Procurement, and Production, which is also known as Plan to Produce business process. The Order Fulfillment and Procurement business are described previously. With the Production business process, you can assess demand, determine supply requirements and allocate production capacity, as well as continually monitor and manage the efficiency of your production process.

Inventory Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Inventory data mart.

Inventory Data Mart Fact Tables

The following table describes the delivered Inventory data mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	Description	Helps Answer
Inventory History Summary Ledger	F_HIST_SUM_LEDG	Stores information on Inventory History Summary ledger. Specifically, this fact table evaluates primary unit of measure quantity, unit of measure unit cost, extended cost, and transaction explanation.	Sum of the quantities on all of the transactions associated with each ledger. Sum of the amounts related to the quantities on all of the transactions associated with each ledger.
Inventory Cycle Count	F_INV_CYCLE_CNT	Stores information on inventory cycle count. Specifically, it stores inventory information at a point in time in which inventory was counted, as well as how often those events occurred. This fact table helps evaluate metrics such as Unit Cost and Adjusted Cost that are calculated during the various cycle counts for a specific invoice.	Total adjusted cost and adjusted quantities per cycle count by store, inventory item, or business unit. Adjusted quantity for a cycle count. Absolute value of a quantity that is adjusted. Unit cost for a cycle count. Adjusted cost associated with the adjusted quantity for a cycle count. Absolute value of the adjusted cost associated with the adjusted quantity for a cycle count.
Inventory Ledger	F_INV_LDGR	Stores information on Inventory ledger. With this fact you can evaluate primary unit of measure quantity, unit of measure unit cost, extended cost, and transaction explanation.	Total quantity of items involved in the transaction in the primary unit of measure. Sum of all of the quantities associated with a ledger transaction. Unit cost of each item measured in the primary unit of measure. Sum of the amounts for all transactions associated with a ledger. Total cost associated with each transaction on a ledger.
Inventory Transaction	F_INV_TRANS	Stores inventory transaction information, such as Demand, Received, Issued, Quantity on Hand, Backorder, Purchase Order, Work Order Received, Hard Committed, Soft Committed, Future Commit, Units in Transit, Units in Inspection, and Inter Unit Transfer Quantity and Amount. This fact table also helps evaluate item cost and Quantity on Hand Cost.	Count of the quantity demanded for a transaction. Number of units involved in a transaction. Quantity demanded for a transaction. Count of the quantity issued for a transaction. Count of the quantity acquired through interunit transfers for a transaction. Count of the quantity on the purchase order for a transaction. Count of quantity as a soft commit on a work order. Quantity on loan to manufacturing.
Physical Inventory	F_PHYSICAL_INV	Stores information on physical inventory measures, such as Open, Received, Hold, and Rejected Quantities. The measures are by inventory location, item and date dimensional criteria. That is, you can evaluate physical inventory by its location, by a specific item, or by a specific date or date range, or by all of these criteria.	Number of available items for a transaction. Number of restricted items not available for a transaction. Number of items on hold for a transaction. Number of rejected items on a transaction.

Inventory Data Mart Dimension Tables

The following table describes the delivered Inventory data mart dimension tables.

Dimension Name	Dimension Record Name	Description
Country of Origin	D_CNTRY_OF_ORIG	Stores country of origin.
Inventory Status Reason	D_INVST_REASON	Stores inventory status reason.
Item Branch	D_ITEM_BR	Stores item branch.
Pack Size	D_PACK_SIZE	Stores pack size.
Stock Type	D_STOCK_TYP	Stores stock types.

Manufacturing Data Mart

In today's manufacturing environments, one of the challenges facing product managers, manufacturing managers, and operations analysts is understanding how production and machine schedules will meet orders and forecasted demand across multiple plants or production lines. The Manufacturing data mart enables you to analyze your production schedule effectively to make informed decisions and ultimately optimize plant, equipment, and production investments. The capability provides the visibility into cost, reliability, timeliness, and efficiency of your manufacturing operations. Armed with this level of insight, you will be able to determine planned production downtime and product availability, calculate scrap costs for plant-level items, and capture production lead times by work order. With the Manufacturing data mart, you can answer questions such as:

How effectively is production capacity being utilized
How much time does it take to complete work orders
scrap cost per product Per business unit
How much downtime can be scheduled for maintenance
What are the manufacturing lead times by work order

Production Business Process

The Manufacturing data mart supports the Production business process, which is also known as the Plan to Produce business process. This business process is described previously. The Production business process includes a production plan or schedule that identifies the type and amount of products to be manufactured over a given time period. Depending on the type of production, work orders or schedules are issued to the shop floor to commence production. Production is tracked through the production line until the order is complete. The completed production is either placed into inventory (make-to-stock) or may be shipped directly to the customer (make-to-order).

Manufacturing Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Manufacturing data mart.

Manufacturing Data Mart Fact Tables

The following table describes the delivered Manufacturing data mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	Description	Helps Answer
Production Costs	F_PRDN_COST	Stores information on work order production costs.	Cost of producing a product, and which components are the most costly. Standard number of units that can be produced, measured in the primary unit of measure. Actual cost associated with the actual number of units that can be produced, measured in the primary unit of measure. Planned number of units that can be produced, measured in the primary unit of measure. Cost associated with the planned number of units that can be produced, measured in the primary unit of measure. Number of scrapped units that can be produced, measured in the primary unit of measure.
Work Order Master	F_WO_MASTER	Stores information on work orders such as the date and quantity of work order.	Number of work orders due for a specific date, percent complete of those orders, and amount of scrap being produced Number of units requested on the work order, measured in the primary unit of measure. Date a work order was ordered. Date a work order started. Date a work order was completed.
Work Order Part List	F_WO_PARTLIST	Stores part list information for work orders.	Do I have enough parts to complete the scheduled production at a work center. Available part list quantity available. Quantity ordered for the part list measured in the primary unit of measure.
Work Order Routing	F_WO_ROUTING	Stores information on work order routing such as queue time, runtime, and labor time.	Standard number of hours required to move units per work order. Standard number of hours that the item on a work order is in the queue. Standard number of hours that a machine is required to run for a work order. Standard number of hours required to set up the labor for a work order. Actual number of machine work hours reported for a work order. Actual number of labor work hours reported for a work order.
Work Order Time Transaction	F_WO_TIME_TR	Stores information on the time transaction of work orders.	Do I have enough work hours to complete the scheduled production at a work center. Capacity of the work of a work center on a given day.
Work Center Master	F_WRKCNTR_MST	Stores information related to work center (work center is analogous to a production area).	Work center effectiveness and total capacity of a work center. Utilization capacity at which a work center can be used. Rate of efficiency at which a work center can be used. Standard capacity at which a work center can be used.
Work Center Resource Unit	F_WRKCNTRESUT	Stores resource information related to a work center.	Capacity of a work center on a given day.

Manufacturing Data Mart Dimension Tables

The following table describes the delivered Manufacturing data mart dimension tables.

Dimension Name	Dimension Record Name	Description
Category 2	D_CATEGORY_02	Stores Category 02 information; indicates the type or category of a work order.
Category 3	D_CATEGORY_03	Stores Category 03 information; indicates the type or category of the work order.
Component by Product	D_CO_BY_PRDCT	Stores component by product. A code that distinguishes standard components or ingredients from co-products, byproducts, and intermediates. Co-products are (concurrent) end items as the result of a process. Byproducts are items that can be produced at any step of a process, but were not planned. Intermediate products are items defined as a result of a step, but are automatically consumed in the following step.
Cost Type	D_COST_TYPE	Stores cost types. It includes a code that designates each element of cost for an item.
Dispatch Group	D_DISPTCH_GRP	Stores dispatch groups. It includes a category code used to group work centers within an over business unit.
From Grade	D_FROM_GRADE	Stores from grade. It indicates the minimum grade that is acceptable for an item.
Hour Type	D_HOUR_TYPE	Stores hour types. It includes a code that indicates the type of time entered. Valid values are: 1, Run Labor Hours 2, Setup Labor Hours 3, Machine Hours 4, Quantities Completed 5, Quantities Scrapped 9, Miscellaneous (such as piece rate bonus)
Operation Type	D_OPERTN_TYPE	Stores operation types. It indicates the type of operation. Valid values include: A - Alternate routing TT- Travel time IT - Idle time T- Text
Phase	D_PHASE	Stores work order phase. It indicates the current stage or phase of development for a work order. You can assign a work order to only one phase code at a time.
Planner	D_PLANNER	Stores manufacturing planner - the address book number of a manager or planner.
Prime Load Code	D_PRIME_LOAD_CD	Stores prime load. It determines if a work center is machine or labor intensive. The system also uses prime load codes in resource requirements planning and capacity requirements planning calculations to develop load profiles.
Parent Child Relationship	D_PRNT_CHLD_REL	Stores the parent-child relationship between work order items. It specifies whether the system displays parent records or child records.
Shift Code	D_SHIFT_CD	Stores manufacturing shift codes. It identifies daily work shifts.
Supervisor	D_SUPERVISOR	Stores supervisor, including the address book number of the supervisor.
Time Basis	D_TMEBASIS_CD	Stores manufacturing time basis codes. It indicates how machine or labor hours are expressed for a product. Time basis codes identify the time basis or rate to be used for machine or labor hours entered for every routing step.
Variance Flag	D_VARIANCE_FLAG	Stores the manufacturing variance flag. 1 indicates that this work order has had work order activity run against it in update mode. 2 indicates that this work order has had the appropriate variances calculated and reported to the general ledger.
Work Order Status	D_WO_STATUS	Stores work order status. It describes the status of a work order, rate schedule, or engineering change order. Any status change from 90 through 99 triggers the system to automatically update the completion date.
Work Order Type	D_WO_TYPE	Stores work order types. It indicates the classification of a work order or engineering change order.
Work Date	D_WORKDATE	Stores manufacturing work dates.

Supply Chain Planning Data Mart

Supply Chain Planning applications identify the right supply of materials to the right place, at the right time, at the lowest cost. The supposed optimization algorithms match product demand with supply to determine the most cost effective method to fulfill the requirements. The plan may recommend transfers of supply from other inventory locations, new purchases from suppliers, and the production of new products to meet the demand. If products must be produced, the planning application may also recommend the appropriate manufacturing sequence. Often, these recommended plans are altered to fit the conditions "on the floor." These deviations cause the performance-against-plan to deteriorate and in extreme cases, make the plan unusable. To mitigate this problem, PeopleSoft has developed the Supply Chain Planning data mart to provide visibility into the compliance of the plan to the actual transfer, purchase, or production. Supply Chain Planning Mart enables you to analyze the deviation from the recommended plan to actual order fulfillment, purchasing, production, inter unit transfers, inventory levels, and capacity utilization.

The Supply Chain Planning data mart helps you answer questions such as:

How much has the plan changed from actual execution
Which products are most out of compliance
Which business units change the plan the most often

Production Business Process

The Supply Chain Planning data mart supports the Production business process, which is also known as the Plan to Produce business process.

Supply Chain Planning Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Supply Chain Planning data mart.

Supply Chain Planning Data Mart Fact Tables

The following table describes the delivered Supply Chain Planning data mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	Description	Helps Answer
Capacity	F_SCP_CAPACITY	Captures the daily capacity utilization associated with production at a specific work center. All the measures are 'bucketed' by time to match planning information. The grain of the fact table is captured at the work center daily production level.	Planned production capacity for a work center and actual production capacity for a business unit. Actual utilized capacity in time units. Actual production capacity in time units. Planned utilized capacity in time units.
Inventory	F_SCP_INVENTORY	Captures daily inventory quantities. All the measures are 'bucketed' by time to match planning information. The grain of the fact table is captured at the daily inventory balance level.	Planned inventory quantity. Actual ending inventory quantity.
Production	F_SCP_PRODN	Captures the quantities and costs associated with production against a specific work order. All the measures are 'bucketed' by time to match planning information. The grain of the Production fact table is captured at the completed work orders level.	Planned production quantity for a work center and actual production quantity for a business unit. Actual production quantity at each work center. Actual production unit cost. Planned production quantity.
Purchasing	F_SCP_PURCHASNG	Captures the quantities and costs associated with purchase orders. All the measures are 'bucketed' by time to match planning information. The grain of the fact table is captured at the received and accepted purchase order line level.	Planned purchasing quantity for a business unit on a particular date and actual purchasing quantity for a supplier for a particular item. Actual purchase order quantity for each line item. Actual voucher cost. Planned purchase order quantity. Quantity accepted on the purchase order receipt. Quantity received on the purchase order receipt. Quantity rejected on the purchase order receipt.
Sales Forecast	F_SCP_SLS_FCST	Captures the product quantities associated with shipments against sales orders and how they compare with predictions based on historical analysis. All the measures are 'bucketed' by time to match planning information. The grain of the Order Shipment fact table is captured at the per item sales for a particular forecasting bucket level.	Planned sales quantity for a specific forecast time bucket. Predicted sales quantity for a specific time period.
Transfers	F_SCP_TRANSFERS	Stores information on the quantities associated with a transfer order between inventory business units. All the measures are 'bucketed' by time to match planning information. The grain of the fact table is captured at the received inventory transfer order level.	Planned inter unit transfer quantities for the business unit and actual inter unit transfer quantities for an item on a specific date. Planned inter unit transfer quantity. Actual inter unit transfer quantity.
Sales Actual	F_SCPSLS_ACTUAL	Stores information on actual sales facts. All the measures are 'bucketed' by time to match planning information.	Actual sales quantity for a particular customer. Actual shipped quantity on a specific sales order.

Supply Chain Planning Data Mart Dimension Tables

The following table describes the delivered Supply Chain Planning data mart dimension tables.

Dimension Name	Dimension Record Name	Description
Bucket	D_BUCKET	Stores supply chain time bucket information.
Work Center	D_WORK_CENTER	Stores Supply Chain Planning related manufacturing work center.

Shared Dimensions

Certain dimensions, such as Account or Department are used across all EPM warehouses. These dimensions are identical in structure and content across all EPM warehouses. The following table describes the delivered shared dimension tables.

Dimension Name	Dimension Record Name	Description
Account	D_ACCOUNT	Stores details of an account that represents a ChartField.
AP Document Type	D_AP_DOC_TYPE	Stores details about AP document types, such as Payables Payments, Payables Adjustments, Payables Accruals, and so on.
Association Type	D_ASSOC_TYPE	Defines the association type for Case, Interaction and Order association.
Bank Account	D_BANK_ACCT	Store details about banks and bank accounts.
Book Code	D_BOOK_CODE	Stores details about book codes, which represent an account attribute and a balancing ChartField.
Budget Reference	D_BUDGET_REF	Stores budget descriptions.
Buyer	D_BUYER	Stores information on buyers, including information related to a buyer's employee ID and address.
Contract	D_CA	Stores the details of the contract information entered with customers. A contract contains the agreement information and obligations for the products and services licensed in the contract and is grouped by contract type.
Carrier	D_CARRIER	Stores information on carriers.
Certification Source	D_CERTSRC	Stores information on certification sources for suppliers.
Channel	D_CHANNEL	Stores channel information related to sales and procurement.
Chartfield1	D_CHARTFIELD1	Stores user defined ChartField details.
Chartfield2	D_CHARTFIELD	Stores user defined ChartField details.
Chartfield3	D_CHARTFIELD3	Stores user defined ChartField details.
Channel Partners	D_CHNL_PARTNER	Stores information about channel partners involved in the sales process.
Expenses Classifications	D_CLASS_FIELD	Stores expenses classification codes and descriptions, such as wages, benefits, health, and office supplies.
Company	D_CMPNY	Stores company-related information.
Credit Risk	D_CREDIT_RISK	Classifies credit risk values as High, Low, and Medium.
Customer Contact Person	D_CUST_CNTCT	Stores information about the customer contact person, which includes contacts and partners.
Customer Organization	D_CUST_ORG	Stores information related to customer organizations (companies). A customer organization is a company that purchases, leases, or contracts for products or services. The customer organization (company) is a subset of the Customer dimension.
Customer Person	D_CUST_PERSON	Stores information about individuals that purchase, lease, and contract for products or services. The Customer Person is a subset of the Customer dimension.
Customer Site	D_CUST_SITE	Stores information about organizations that purchase, lease, and contract for product or services located at a particular site or location. Sites can be an organization site or an individual site. Site is also a subset of the Customer dimension.
Customer Master	D_CUSTOMER	Stores information for entities that can participate in business relationships.
Department	D_DEPT	Stores information about the entities in an organization. This dimension includes attributes about a department, such as description, company code, location, and budget fields.
Employee Job Code	D_EMPL_JOB	Stores employee job history data, such as actions taken, department, job code, location, and salary history. Multiple records can be created for an employee.
Establishment	D_ESTAB	Stores distinct physical places of business (establishments) within a company and its address, and is used for regulatory reporting purposes.
Frequency	D_FREQ	Stores the payment and hours reporting frequency for time and payroll data. You can use a frequency to indicate how many times per year an event occurs.
Fund	D_FUND	Stores details about fund codes and their description.
GL Adjustment types	D_GL_ADJ_TYPE	Stores types of general ledger (GL) adjustments.
GL Offset	D_GL_OFFSET	Stores information on GL offset. This dimension groups billing information, such as office rent and retail rent.
Industry Group	D_INDUSTRY_GRP	Stores customer industry group information.
Inventory Item	D_INV_ITEM	Stores information about Inventory Item, which includes all attributes of item, including simple hierarchy information, such as category or group, as well as Make or Buy flag.
Inventory Location	D_INV_LOCATION	Stores information about the storage location from which goods will be moved.
Jobcode	D_JOBCODE	Stores information about the job assignments in an organization. This dimension represents the categorization of jobs into types, such as executive, technical, and administrative services.
Journal Line Source	D_JRNL_SOURCE	Stores the details about source of journal entries created in GL.
Sales Lead	D_LEAD	Stores sales leads generated by marketing campaign waves.
Ledger	D_LEDGER	Stores the ID and description of ledgers that are defined based on templates.
Line Type	D_LN_TYP	Stores information on line types.
Location	D_LOCATION	Stores a list of work sites for an organization. Location is used to establish physical locations in an organization, such as corporate headquarters, branch offices, and remote sales offices.
Lot	D_LOT	Stores information on lot (a group of items with similar characteristics).
Operating Unit	D_OPER_UNIT	Stores details about operating units, such as a plant, office, physical location, branch, and building.
Sales Opportunity	D_OPPORTUNITY	Stores information about a sales opportunity.
Order Capture	D_ORD_CAPTURE	Stores order capture information for the sales order process.
Sales Order Status	D_ORD_STAT	Stores information on order status.
Partner	D_PARTNER	Stores partner information. The dimension has the following hierarchy: Partner, Partner Status.
Pay Group	D_PAYGRP	Groups employees by how they are paid.
Person	D_PERSON	Stores the most current personal information of both employees and non-employees of an organization.
AR Specialist	D_PERSON_ARSPL	Stores details, such name and contact, about the accounts receivable (AR) specialist involved in handling the disputes and deductions in the AR module.
AR Collector	D_PERSON_COLTR	Stores details, such name and contact, about the AR collector involved in collecting the receivables amount in the AR module.
AR Credit Analyst	D_PERSON_CRNYST	Stores details, such name and details, about the AR credit analyst involved in handling the credits given to customers.
AR Deduction Manager	D_PERSON_DEDMGR	Stores AR deduction manager name and contact information.
Position	D_POS	Stores information on all job positions available, whether an employee fills the position or no, and helps with data analysis based on salary or standard hours.
Product Group	D_PROD_GROUP	Stores information on product groups.
Product	D_PRODUCT	Stores information on products.
Program	D_PROGRAM_FDM	Keeps track of programs, such as public works, social services, fire, and public safety, that are tracked in General Ledger.
Project	D_PROJECT	Stores information about projects. A project is a vehicle for identifying an initiative that has a specified start and end date.
Partner Contact	D_PRTR_CNTCT	Stores partner contact data.
Payment Method	D_PYMNT_MTHD	Stores methods of payment, such as check, cash, and credit card.
Receive Line Status	D_RECLN_STATUS	Stores information on all receive line statuses.
Regulatory Region	D_REG_RGN	Stores the codes for regulatory and regional edit purposes. A regulatory region is any region where there are specific laws and regulations that are used for transactional purposes.
Geographic Region	D_REGION	Contains geography information for customers.
Salary Plan	D_SALPLN	Stores unique salary categories that are defined in an organization. These categories are set up according to an employee's compensation structure.
Scenario	D_SCENARIO	Stores details of historical, budgeting, and forecast scenarios.
Customer Segment	D_SEGMENT	Stores customer segment information.
Statistics Code	D_STAT_CODE	Stores details about statistical information, such as floor space, full-time equivalent workdays, and shipment size.
Subledger	D_SUBLEDGER	Stores information on subledger, which groups the accounting information.
Supplier	D_SUPPLIER	Stores information on suppliers, such as remit to supplier and corporate supplier.
Sales Territory	D_TERRITORY	Stores sales territory information. Sales territories are user defined sales regions independent of geography or proximity.
Unit	D_UNIT	Stores detail information on real estate properties.
Unit of Measure	D_UOM	Indicates the quantity in which an inventory item is expressed, such as case (CS) or box (BX).