Reports

On-Time In-Full (OTIF) Report

Stop Late Shipments: The "At-Risk" Delivery Monitor for IFS Cloud
Product ID: REP-INV-002 | Category: Supply Chain

At-Risk Customer Delivery Watchlist

Technical specification and functional description for the "At-Risk" delivery monitoring report.

Status: Released Type: Quick Report IFS Cloud

1. Document Information

1.1 Modification Specification

Name At-Risk Customer Delivery Watchlist
ID REP-INV-002
Component Supply Chain Management (SCM)
Complexity Low (SQL Quick Report)

1.2 Document History

Version Date Author Description of Change
1.1 2024-05-20 IFS-ERP.com Expanded filters (Salesman, Price Groups, Stat Groups) and added descriptive output columns.

2. Open Issues / Required Decisions

Null Handling: The report currently defaults NULL Salesman and NULL Coordinator names to 'No Salesman' and 'No Coordinator' respectively to ensure they appear in the grid results.

3. Introduction

3.1 Purpose

The primary purpose of this report is to provide Customer Service Representatives (CSRs) and Logistics Planners with a "Safety Net" visibility tool. It identifies Customer Order lines that are due for delivery immediately (Today/Tomorrow) but have not yet progressed to a "Picked" or "Delivered" state.

3.2 Alternative Solution

Currently, users must manually navigate to the Overview - Customer Order Lines window, query by date range, and filter out shipped lines. This manual process is reactive and prone to human error.

3.3 Scope

The report covers all Outbound Customer Orders across defined Sites (Contracts). It includes logic to look forward (Planning) and backward (Catching missed deliveries).

3.4 Limitations

The report currently excludes orders that are "Blocked" at the header level, as these are administrative holds rather than logistical failures.

3.5 Attachments

SQL Source Code: The full SQL query logic is available in the download package.

4. Functional Description

Key Features
  • Dynamic Time Window: Uses variables &DaysPast and &DaysFuture to create a rolling window of interest.
  • Comprehensive Filtering: Supports filtering by Salesman, Coordinator, Customer Group, Price Group, and Sales Group for detailed analysis.
  • Name Resolution: Displays readable names for Salesmen and Coordinators instead of just ID codes.
OTIF report - screenshot with filters

Selection Parameters (Filters)

Parameter Description Default Value
&DaysPast Number of days to look back for missed orders. 7
&DaysFuture Number of days to look ahead for upcoming orders. 1
&Contract Site / Contract filter. % (All)
&CustomerNo Customer ID filter. % (All)
&CustomerStatisticsGroup Filter by Customer Statistics Group. % (All)
&CustomerPriceGroup Filter by Customer Price Group ID. % (All)
&Salesman Filter by Salesman Name. % (All)
&PartNo Filter by Part Number. % (All)
&SalesGroup Filter by Sales Group (Product Group). % (All)
&SalesPriceGroup Filter by Sales Price Group. % (All)
&CoordinatorName Filter by Order Coordinator Name. % (All)

Report Output Columns

Column Name SQL Alias Description
Order No col.order_no Unique identifier for the Customer Order.
Line No col.line_no Line number of the order.
Rel No col.rel_no Release number of the order.
Customer No col.customer_no The customer ID.
Customer Name customer_name The full name of the customer.
Cust Stat Grp Customer_Statistics_Group Customer Statistics Group ID.
Cust Price Grp Customer_Price_Group Customer Price Group ID.
Salesman Salesman_Name Name of the Salesman (defaults to 'No Salesman' if null).
Part No part_no The Inventory Part number.
Part Description Part_Description Description of the part catalog.
Sales Grp Sales_Group Catalog Group / Sales Group.
Sales Price Grp Sales_Price_Group Sales Price Group associated with the part.
Qty Due col.buy_qty_due Quantity required to be shipped.
UoM UnitMeas Sales Unit of Measure.
Qty Reserved col.qty_assigned Current quantity reserved/assigned.
Qty Picked col.qty_picked Current quantity picked.
Planned Del Date col.planned_delivery_date Calculated delivery date.
Status col.state Current line status (e.g., Released, Reserved).
Coordinator Coordinator_Name Name of the Order Coordinator (defaults to 'No Coordinator' if null).
Site col.contract Site / Contract ID.
The Excel vwith exported data from OTIF report

5. Technical Description

This report is implemented as a SQL Query designed for the IFS Cloud "Quick Reports" functionality. It is optimized for performance by utilizing indexed columns on the CUSTOMER_ORDER_LINE table.

5.1 Database Object Usage

The report aggregates data from the following logical units (Views):

  • CUSTOMER_ORDER_LINE (col): Primary driver for line status and dates.
  • CUSTOMER_INFO (c): Retrieves Customer Name.
  • CUSTOMER_ORDER (co): Header details for Coordinator and Salesman linkage.
  • PERSON_INFO (person): Resolves Person IDs to Full Names.
  • SALES_PART (s): Retrieves Sales Groups and Price Groups.

5.2 Technical Logic

  • Joins: Uses LEFT JOIN for ORDER_COORDINATOR and PERSON_INFO to ensure orders without assigned coordinators/salesmen are still displayed.
  • Functions: Utilizes NVL for parameter handling, ensuring wildcard (%) defaults.
  • Date Logic: Uses SYSDATE logic (BETWEEN SYSDATE - X AND SYSDATE + Y) for relative dynamic ranges.

6. Delivery Terms

Price

€250

One-time payment.

Format

.sql source code & .pdf guide.

Support

30 Days email support included.

License

Single Entity License. Test & Prod use.

Frequently Asked Questions (FAQ)

The product is delivered as a SQL script. You simply navigate to the Quick Reports window in IFS Cloud Solution Manager, create a new report of type "SQL Statement", and paste the provided code. No restarts or complex deployments are required.

Yes. Since the source code is open SQL, you can easily add extra columns (like Delivery_Terms or Weight) by modifying the SELECT statement before publishing.

Yes. The views used (e.g., CUSTOMER_ORDER_LINE) are standard across IFS versions. While this product is tagged for IFS Cloud, the SQL logic is typically backward compatible with Apps 10 and Apps 9.

Single License

€250

One-time investment for perpetual use on your IFS instance.

What is included:
  • Full SQL Source Code (.sql)
  • PDF Deployment Guide
  • 30-Day Installation Guarantee
  • Update-Safe for IFS Cloud
Purchase & Download

Frequently Asked Questions

Common questions about the "At-Risk" Delivery Watchlist Report

Deployment is near-instant. Once you have the SQL script, you simply paste it into the Quick Reports window in IFS Cloud. Total setup time is typically under 10 minutes.

Yes. This report uses standard SQL views provided by IFS (Evergreen-compliant). It does not rely on custom C# code or internal core modifications, making it completely "Update-Safe."

Absolutely. We provide the full source code. If you have internal technical staff, they can easily modify the SELECT statement to add custom attributes or unique business logic specific to your site.

No. The license is per IFS Instance (Entity). Once installed, any user in your organization with the appropriate security permissions can run the report as often as needed.