Migrating Custom PL/SQL to IFS Cloud: Rearchitecting for the Cloud

The transition from IFS Applications 10 to IFS Cloud is more than a technical version upgrade. The application still uses Oracle and generated PL/SQL extensively, but the supported source, build, deployment, user experience, API, and managed-service boundaries have changed. For organisations carrying years of custom packages, functions, and procedures, the right conclusion is not “PL/SQL has gone”; it is that each customisation needs to be reconciled with the Cloud source model and delivery process rather than copied into production by hand.

This article walks developers and architects through the reality of migrating custom PL/SQL to IFS Cloud, what's actually supported, what requires rearchitecting, and the cloud-native alternatives that will define your migration strategy.

The Core Challenge: PL/SQL in Apps 10 vs. IFS Cloud

What IFS Applications 10 Allowed

In IFS Applications 10, developers had direct access to the Oracle database and were encouraged to extend functionality through custom PL/SQL packages:

• Database-level customisation: Many customers deployed approved custom packages, functions, and procedures to an on-premises application owner schema, although source control and IFS development tooling were still preferable to ad-hoc production changes.
• Stateful database connections: Transactions could be grouped, rolled back, and managed within a single session.
• SOAP/PL/SQL web services: Expose PL/SQL methods as WSDL-based web services for external integration.
• Scheduled database tasks: Use database scheduling to invoke custom procedures on a timer.
• Direct database integrations: Some customers allowed reporting tools to query views and integrations to call PL/SQL APIs. Direct writes to IFS base tables were unsafe even in Apps 10 because they bypassed validation, state handling, security, and side effects.

This flexibility came at a cost: tight coupling between custom code and the data layer, difficult upgrades, and increased security exposure.

The IFS Cloud Paradigm Shift

IFS Cloud is built on a fundamentally different set of principles:

• API-first external architecture: Supported external consumers normally use projection-based REST/OData APIs, IFS Connect, files, or another documented integration surface rather than the application database.
• Stateless communication: OData and REST APIs are stateless. You cannot group multiple API calls into a single database transaction; each request is independent.
• IFS source models plus generated/runtime code: Entity, enumeration, projection and client models use IFS source formats often grouped under the “Marble” name. Server-side implementation and generated logical-unit APIs still use PL/SQL; Marble is not a C# or Java runtime language.
• Layered customisation: Customer source is maintained in an appropriate customer component/layer and built against a defined IFS Cloud release rather than patched into IFS-delivered Core files.
• Managed database boundary: In IFS-managed Cloud service environments, customers do not treat the production database as an integration or development endpoint. Other deployment models and non-production arrangements vary by contract, so establish the supported access boundary for the target estate.

This shift prioritizes security, scalability, and upgrade stability over raw developer flexibility.

What PL/SQL Is (and Isn't) Supported in IFS Cloud

What You Can Still Do with PL/SQL in IFS Cloud

PL/SQL remains a first-class server implementation technology inside IFS Cloud. What changes is how customer code is sourced, built, delivered, and exposed:

1. Custom utility packages: You can write PL/SQL packages that live in your custom module and contain helper logic for calculations, validations, or data transformations.

2. Projection actions and functions: A modelled projection action/function can be implemented by generated or customer-layer server code, providing a permission-controlled API contract. Do not expose an arbitrary package wholesale.

3. Data migration support: In an approved migration environment, transformation or validation code may complement IFS migration tooling. The production managed-service database is not a general-purpose staging area, and business objects should be loaded through supported interfaces.

4. Model-driven business logic with PL/SQL implementation: Entity/projection models define the contract; generated APIs, customer-layer overrides, and explicitly declared methods implement server behaviour.

5. Custom Logical Units (LUs): You can define custom LUs with custom tables and associated PL/SQL APIs, but these operate within IFS's managed framework, not as standalone utilities.

What You Cannot Do (or Shouldn't)

1. Direct database manipulation from external tools: In an IFS-managed service, third-party tools should use a supported API, file, replication/data-service, or integration route. OData is important, but it is not the only supported route.

2. Self-managed Oracle scheduler jobs in a managed service: Do not recreate DBMS_SCHEDULER or DBMS_JOB objects behind the service boundary. Use supported IFS database-task/background-job scheduling where available in the target release, or an authorised external scheduler calling a documented API.

3. Stateful database transactions across multiple calls: You cannot group an API call followed by a custom procedure and another API call into a single rollback-able transaction. Each is independent.

4. SOAP/WSDL from custom PL/SQL: There is no automatic WSDL generation from custom PL/SQL code. If external systems need to call your logic, expose it through an OData projection.

5. Patching IFS-delivered database objects directly: Never replace a Core package body in place. If source customisation is justified and licensed for the solution, use a documented customer-layer override/overtake and the IFS build process; otherwise prefer configuration, workflow, events, and projection actions.

Auditing Your Custom PL/SQL: Where to Start

Before you begin a migration project, you need a clear inventory of what you're carrying forward.

Step 1: Document Every Custom Package

Run this query in your current Apps 10 environment:

This query is only a discovery aid: it will include thousands of standard IFS objects and miss source files that are not installed in that database. Reconcile it with the Apps 10 customisation repository, installation logs, method/event metadata, scheduler definitions, grants, synonyms, and actual callers. Use DBA_OBJECTS only through a separately authorised catalogue account; an application developer does not need broad DBA privileges for this inventory.

For each custom package, document:

• Purpose: What business problem does it solve?
• Invocation points: Where is it called from? (Reports, custom menu items, scheduled tasks, third-party systems)
• Dependencies: What tables does it touch? What other packages does it call?
• Complexity: How many lines of code? How many branches (if/else logic)?
• Data volume: Does it process thousands of records at once, or small batches?
• Statefulness: Does it rely on session-level variables, transactions, or temporary tables?

Step 2: Categorize by Migration Path

After documenting, classify each package into one of these buckets:

Retire (Obsolete)

• Packages that compensated for missing standard features in Apps 10 that now exist natively in Cloud.
• Example: A custom invoice approval workflow that's now built into IFS Cloud's standard approval engine.
• Action: Don't migrate. Re-test standard features instead.

Retain and expose deliberately

• Logic that is still needed but doesn't modify core data structures.
• Examples: complex calculations, data aggregations, format conversions, lookup functions.
• Action: Retain pure, supported PL/SQL in customer source where appropriate and expose only the required operation through a projection action/function.

Rearchitect (source models + PL/SQL/API orchestration)

• Business logic intertwined with direct table updates.
• Examples: multi-step workflows, inventory adjustments, order fulfillment steps.
• Action: Replace direct table DML with standard APIs and explicit state transitions. Use the target release's entity/projection models, configured workflow/events, and server methods in the places each belongs.

Refactor for OData (External Integrations)

• Custom SOAP web services or scripts called by third-party systems.
• Action: Build custom OData projections or use IFS Connect/integration adapters.

Step 3: Assess Integration Touch Points

For each package, identify every system that calls it:

• Internal IFS users (menus, batch jobs, reports)
• Third-party ERP modules (payroll, HR, BI tools)
• Custom interfaces (EDI, file drop, API consumers)
• Scheduled jobs (cron, database tasks, message queues)

This determines whether your migration needs new API contracts and who needs to be informed of changes.

Cloud-Native Alternatives to Custom PL/SQL

1. IFS Source Models: Define the Contract

“Marble” is commonly used for the IFS model-source languages handled by Developer Studio. Entity and enumeration models describe persistent domains; projection models describe service contracts; client models describe Aurena pages and commands. The generators then produce database and service artefacts. This modelling avoids a great deal of hand-written plumbing, but it does not replace every PL/SQL implementation.

What Marble Can Do

• Define or extend entities and enumerations in their model files.
• Define projection entity sets, attributes, references, arrays, actions, functions, and structures.
• Define Aurena pages, lists, groups, commands, assistants, and navigation.
• Generate consistent contracts and reduce hand-written CRUD plumbing.
• Layer supported refinements over IFS-delivered source without editing Core files.

Example: Custom Approval Logic

Unsafe legacy pattern:

This is unsafe in Apps 10 as well as Cloud: it bypasses the purchase-order state machine and commits a transaction it may not own.

Cloud design shape:

The action's generated server method must call the documented purchase-order business API/state transition for the target release. It must not update PURCHASE_ORDER_TAB or set ROWSTATE itself. The projection controls the external contract and permission grant; the PL/SQL implementation preserves IFS business logic. Action declaration/implementation syntax can vary by release and generated entity, so use Developer Studio completion and the generated skeleton rather than copying a guessed signature.

2. Configuration, Workflow and Events: Extend Before Customising Source

IFS Application Events can react to published application events, and configured workflows can orchestrate supported business processes in releases where the relevant capability is available. They are not a universal set of C#-style BeforeCreate/AfterCreate entity hooks.

Useful event-design questions

• Does the standard business object publish an event at the required point?
• Is it raised inside the originating transaction or processed later?
• Which event attributes are actually available in this release?
• Must failure block the business transaction, or should work be durably queued?
• Can standard workflow, approval, routing rules, or a custom action meet the requirement more safely?

Example: Auto-Calculating Freight

This separates the IFS state change from a potentially slow remote dependency. Exact event names, action types, substitution attributes, and transaction timing must be verified in the target release and component.

3. OData Projections: Exposing Business Logic via APIs

An IFS projection exposes selected entity sets, attributes, relationships, functions, and actions as a permission-controlled service contract. It is not merely a read/write table view, and CRUD must be limited to operations the underlying entity safely supports.

Example: Custom Invoice Summary Projection

AcmeInvoiceSummary must first exist in the entity/source model, and the fetch expression must be SQL-safe and performant. Treat the names as a design example, not standard IFS objects.

Now, external systems (BI tools, portals, third-party apps) can consume this projection via REST/OData:

GET https://<host>/main/ifsapplications/projection/v1/AcmeInvoiceSummary.svc/InvoiceSummaries?$filter=AgeingDays%20gt%2030

The concrete projection path/version comes from the target environment's API Explorer or service metadata. A projection permission grant controls service access, but sensitive row-level business access must also be enforced by the underlying model; a projection grant alone is not row-level security.

4. Custom Logical Units: Structured Customizations

For durable custom business data, define a customer-owned entity in the registered component, generate its database/server artefacts, and expose only the required fields and operations through a projection.

Entity and enumeration model files are XML-backed in current IFS Cloud Developer Studio tooling; the exact schema is produced and validated by the IDE, so the fragment above is intentionally structural rather than a paste-ready file. A separate projection makes the entity an API resource. Define ownership, keys, references, retention, permissions, and upgrade tests just as carefully as for standard business data.

Migration Patterns: From PL/SQL to Cloud-Native

Pattern 1: Calculation Packages → OData Projections

Old Approach (Apps 10):

• Custom PRICING_API package with functions like Calculate_Discount().
• Called from order entry screens, batch jobs, and external pricing engines.
• Direct SQL updates to order lines.

New Approach (IFS Cloud):

• Retain pure calculation logic in a customer-layer PL/SQL package when that is the cleanest supported implementation.
• Declare only the required calculation as a projection function/action such as AcmePricingCalculation.
• External systems call the OData endpoint; no direct SQL.
• Order changes use the standard IFS pricing APIs and state model; do not create a second writable pricing truth by accident.

Migration Steps:

1. Extract pure logic from the PL/SQL package (remove CRUD, transaction handling).
2. Reconcile public IFS API dependencies and place retained code in the customer source/build structure.
3. Test with the same input/output data.
4. Deploy as a custom module.
5. Update external integrations to use the new OData endpoint.

Pattern 2: Workflow Packages → Events + Custom Actions

Old Approach (Apps 10):

• Custom ORDER_WORKFLOW_API package with procedures like Approve_Order(), Ship_Order().
• Called via menu items, batch jobs, or external systems.
• Performs multiple DML operations in a single transaction.

New Approach (IFS Cloud):

• Reuse standard state transitions and validations wherever possible.
• Use configured workflow/application events only where the target object publishes the needed extension point.
• Use a custom projection action for one cohesive server-side transaction when a supported standard action is insufficient.
• Coordinate genuinely long-running, multi-system work as an explicit workflow or saga with idempotency and compensation.

Migration Steps:

1. Map each procedure to the standard business action and state transition it was trying to reproduce.
2. Identify the configuration, workflow, event, or source extension point that actually exists in the target release.
3. Put atomic IFS work behind one action; do not force the caller to reproduce internal sequencing.
4. For cross-system steps, define idempotency, retry, timeout, and compensation explicitly.
5. Update callers to use the documented projection operation and test permission/row-access behaviour.

Pattern 3: Scheduled Tasks → IFS Cloud Scheduler or Custom Events

Old Approach (Apps 10):

• Custom database tasks scheduled via DBMS_SCHEDULER.
• Run nightly batch jobs that update inventory, generate invoices, etc.

New Approach (IFS Cloud):

• Use an IFS database task/background job and supported schedule where the method and capability are available.
• Or use an authorised external scheduler to call a purpose-built projection action.
• Partition work into bounded, restartable batches; OData requests do not turn many separate calls into one transaction.

Migration Steps:

1. Identify what the scheduled task does (e.g., "finalize unpaid invoices").
2. Prefer one supported server operation for a cohesive IFS transaction; use a migration job only for a migration/load use case.
3. Register the task through the supported IFS UI/API or an external job system, not by creating Oracle scheduler objects behind the service.
4. Test with sample data; verify no orphaned or dangling records.

Pattern 4: Direct Third-Party Integrations → OData Adapters

Old Approach (Apps 10):

• External systems (BI tools, payroll systems) directly query IFS tables via ODBC/JDBC.
• Custom views expose business logic as queryable SQL.
• ETL processes insert/update records via custom APIs.

New Approach (IFS Cloud):

• Use OData projections to expose all queryable data.
• IFS integration capabilities, an enterprise iPaaS, or a customer-owned service can mediate connections where a direct projection call is not sufficient.
• Custom OData projections with validation logic replace custom insert/update procedures.

Migration Steps:

1. Catalog all third-party connections: which tables, what operations (read/write).
2. Design OData projections for each: include security filters, calculated fields, and validation.
3. Register the integration identity and use the OAuth flow supported by the target IFS identity configuration; do not assume every endpoint accepts mTLS.
4. Update third-party tool connections to use OData endpoints.
5. Test data flow end-to-end; validate filters and permissions.

Best Practices for PL/SQL Migration

1. Start with an Inventory, Not Code Rewrite

Before touching a single line of code, have a complete audit. It is common to find obsolete packages after years of upgrades, but use measured call/dependency evidence rather than assuming a fixed 30–40% retirement rate.

2. Separate Data Plumbing from Business Logic

Custom PL/SQL often mixes concerns: data access (SQL), transaction management, and business rules. As you migrate:

• Extract pure business logic (math, conditionals, validations).
• Handle server-side data access through standard IFS APIs and generated entity APIs; use OData for external contracts, not as an internal replacement for every PL/SQL call.
• Let the framework manage transactions.

3. Test Business Logic, Not SQL Syntax

In Apps 10, you tested PL/SQL by running it in SQL Developer and checking the database state. In IFS Cloud:

• Test business logic in isolation (input → expected output).
• Test integration through OData endpoints.
• Use automated test frameworks; avoid manual database checks.

4. Plan for Stateless Design

If a custom package relies on session state (variables, temporary tables, transaction rollback), you'll need to refactor. Options:

• Move session state to the client (pass values in request/response).
• Use a state machine pattern (explicit states stored in the database).
• Break multi-step operations into smaller, chainable operations.

5. Leverage IFS Cloud's Built-In Features First

Before writing custom code, check if IFS Cloud already does it:

• Custom approvals? Use the workflow engine.
• Complex pricing? Check Pricing & Analytics modules.
• Data transformations? Use Data Migration configurations.

Fewer custom code = faster upgrades, fewer bugs, lower TCO.

6. Version and Document Your Custom Modules

• Store custom modules in git (alongside your main codebase).
• Document assumptions, dependencies, and testing procedures.
• Use semantic versioning; tag releases before IFS upgrades.
• Plan testing for every IFS release and service update that the organisation adopts.

Rearchitecture by Example: A Real-World Case

Scenario: Custom Inventory Allocation

Legacy Situation (Apps 10):

• Custom INVENTORY_ALLOCATION_API.Allocate_Stock() procedure.
• Directly updates inventory tables and allocation queues.
• Called by order entry, demand planning module, and nightly batch job.
• Uses a temp table to stage allocation decisions; rolls back if any step fails.
• No audit trail; compliance team unhappy.

Cloud Rearchitecture:

1. Challenge the custom data model first. Stock reservation is standard IFS business logic, so identify the standard reservation/allocation APIs and configuration before creating an AllocationRequest entity. A custom entity is justified only for extra orchestration state that IFS does not already own—for example a request ID, source-system correlation ID, requested policy, processing state, and diagnostic result.

2. Define the custom request in the entity model. Use a stable business key, explicit references to standard objects, a generated enumeration for status, optimistic concurrency, retention rules, and a clear owner. Generate the logical-unit API; do not hand-create a table and generic CRUD package.

3. Implement one cohesive server operation. The customer-layer PL/SQL method validates the request and invokes the target release's public inventory/reservation APIs. It neither updates inventory base tables nor commits. The projection action transaction commits on success and rolls back on an exception.

   Acme_Allocation_Impl_API and the request entity are customer-owned design names. The standard API call is deliberately not guessed here: inventory ownership, part/location keys, serial/lot handling, availability controls, and reservation semantics must be resolved from the target solution.

4. Expose the minimum projection contract. Provide CRUD only for safe request fields, a bound Allocate action, and a read-only result/status. Grant the projection to a dedicated permission set and enforce company/site access in the business layer.

5. Choose synchronous or asynchronous execution explicitly. A small atomic reservation may complete inside the action. Large allocation runs should enqueue bounded work, expose progress, be idempotent by request ID, and resume safely after failure. Do not loop through thousands of OData updates and assume they share a rollback boundary.

6. Integrate at a real extension point. If an order-entry process must initiate allocation, use a supported standard reservation step, configured workflow, published event, or carefully reviewed customer-layer extension point. There is no generic [Marble.Eventhandler("SalesOrderService", "BeforeCreate")] hook.

Outcome:

• No direct inventory-table updates; changes go through the standard IFS reservation logic.
• A durable request provides traceable orchestration state without relying on session variables.
• Pure decision logic can be tested independently, while API/state-transition tests run against a representative IFS environment.
• External systems call a stable OData endpoint; no SQL knowledge required.
• Batch jobs use supported IFS task scheduling or an authorised external caller, not a self-installed Oracle scheduler job.
• Audit evidence is designed and verified; it is not assumed to appear automatically merely because a projection was used.

Key Takeaways

1. PL/SQL remains central inside IFS Cloud. Customer PL/SQL must sit in the supported source/layer/build model, while projections, client models, configuration, workflow, and events provide complementary extension surfaces.

2. Start with a rigorous audit: Catalog what you have, classify it, and retire what's obsolete. This reduces avoidable rework; record the items retired, redesigned, or carried forward so the benefit is measurable.

3. Design API conversations as stateless: each HTTP request has its own transaction. A projection action can still perform an atomic server-side unit of work; long-running cross-system workflows need durable state, idempotency, and compensation.

4. Learn the IFS model sources and generated architecture: entity, enumeration, projection, and client sources are essential, but so are layered PL/SQL, application security, and delivery tooling.

5. Choose the smallest supported extension surface: standard configuration or workflow first, published events where they fit, projection actions for API operations, and source customisation only when justified.

6. Plan for integration re-architecture: Third-party tools that query IFS tables directly must be updated to use OData. This is a people and process change, not just technology.

7. Budget from evidence, not a generic rewrite percentage: some pure PL/SQL can be retained with dependency changes; direct-DML integrations and obsolete UI/workflow work may need complete redesign. Estimate after inventory and fit-gap analysis.

The migration from Apps 10 PL/SQL to IFS Cloud is a chance to remove dead code and restore supported boundaries. The best outcome is not “no PL/SQL”; it is the least custom code necessary, modelled and delivered correctly, with explicit API contracts and repeatable upgrade tests.