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. |
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:
|
Operation Type |
D_OPERTN_TYPE |
Stores operation types. It indicates the type of operation. Valid values include:
|
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. |