Product Activity Report - SIRESRPA

The Product Activity Report provides a detailed overview of each product's activity over the past twelve months, organized by warehouse. The report defines the twelve-month period differently for stock outs and other fields:

• Stock Outs: From the first day of the current month minus 12 months, up to today.

• Other Fields: From the current day minus 1 year, up to today.

Example:
If the report is run on February 15, 2025:

• Most fields are calculated using data from February 16, 2024 to February 15, 2025.

• Stock outs are calculated using data from February 1, 2024 to February 15, 2025.

Stock Outages

This section examines the sia_activity_dtl table for the selected warehouse. For each product ID, it sums the values in the sia_stock_out column for records posted within the last twelve months from the report's run date.

Service Level Percentages

Service level percentages are calculated using the following steps:

1. Data Extraction

   • Reads order item detail records from the sim_item_dtl table.

   • Writes the data to a file for sorting.

2. Sorting

   1. Sorts the data by order, line number, and date to prioritize the oldest attempt to fill each line item.

3. Service Level Calculation

   • Only the first record for each product line item is used.

   • For each product, the earliest attempt to fill the line item on each store’s order is selected.

   • The Quantity Shipped (sim_qnty_shp) is divided by the Quantity Ordered (sim_qnty_ord).

   • These values are totaled, and a counter is incremented for each line item processed, for total lines processed.

   • The final Service Level Percentage is calculated as: Service Level %=(Number of Line ItemsTotal of Shipped/Ordered Ratios​)×100/ Total number of line items that were processed.

Example Calculations

Simple Example

Product: PAPER01

Orders:

• Store SI001234 ordered 100 units; 100 units were shipped.

• Store SI004567 ordered 500 units; 500 units were shipped.

Calculation:

• SI001234: 100 (order) / 100 (shipped) = 1.00

• SI004567: 500 (order) / 500 (shipped) = 1.00

• Total (order/shipped ratio) = 1.00 + 1.00 = 2.00

• Count (line items processed) = 2

Service Level% = 2(total order/shipped ratio) * 100 = 200 / 2 (count) = 100%

Complex Example

Product: PAPER02
Orders:

• Store SI003456 ordered 100 units; 100 units were shipped.

• Store SI005498 ordered 50 units; 30 units were shipped.

Calculation:

• SI003456: 100 (order) / 100 (shipped) = 1.00

• SI005498: 30 (order) / 50 (shipped) = 0.60

• Total (order/shipped ratio) = 1.00 + 0.60 = 1.60

• Count (line items processed) = 2

Service Level% = 1.60 (total order/shipped ratio) * 100 = 160 / 2 (count) = 80%