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

The transition from IFS Applications 10 to IFS Cloud is not simply a version upgrade. It's a fundamental shift in architecture, deployment model, and how custom business logic is implemented. For organizations carrying years of custom PL/SQL packages, functions, and procedures, this shift presents a critical challenge: your legacy database-bound code won't just "lift and shift" to the cloud.

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:

• Direct database customization: Write and deploy custom packages, functions, and procedures directly to the application schema.
• 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 table access: Third-party systems could query and modify IFS tables using SQL and PL/SQL calls.

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 architecture: Everything accessible to external systems—and increasingly, to custom code—flows through REST/OData APIs, not direct database access.
• Stateless communication: OData and REST APIs are stateless. You cannot group multiple API calls into a single database transaction; each request is independent.
• Marble domain-specific language: Custom business logic is now expressed in Marble, a low-code, declarative language, complemented by PL/SQL in specific, managed contexts.
• Microservices-inspired design: Custom features live in custom modules or components, separate from the core IFS framework.
• No direct database access: In cloud-managed deployments (SaaS), your team cannot access the production database at all. Even in customer-managed clouds, database access is restricted to defined migration and maintenance windows.

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

Custom PL/SQL packages are not forbidden in IFS Cloud, but their role has narrowed significantly:

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. Exposing custom packages via OData projections: A custom PL/SQL package can be called from a custom projection (an OData endpoint), allowing external systems to invoke your logic through a documented, stateless API.

3. Data migration support: During data migration phases, custom PL/SQL can prepare data in intermediate tables, perform ELT (Extract-Load-Transform) processes, and validate incoming records before they're committed to the application.

4. Business logic in Marble with PL/SQL integration: Marble models (entities, projections, services) can call custom PL/SQL functions for complex calculations or multi-step operations.

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: Third-party ETL tools, BI platforms, and integration engines cannot directly query or update IFS tables. They must use OData APIs.

2. Scheduled PL/SQL tasks on production: In cloud-managed environments, you don't have access to the database scheduler. In customer-managed environments, scheduled tasks are restricted to avoid interfering with IFS processes.

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. Replacing standard business logic with PL/SQL customizations: Overriding core IFS PL/SQL functions (like Order_API.Create) directly is not supported. Use events, custom actions, or Marble extensions instead.

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:

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.

Rehost (OData Wrapper)

• Logic that is still needed but doesn't modify core data structures.
• Examples: complex calculations, data aggregations, format conversions, lookup functions.
• Action: Wrap in a custom projection and expose as OData.

Rearchitect (Marble + Limited PL/SQL)

• Business logic intertwined with direct table updates.
• Examples: multi-step workflows, inventory adjustments, order fulfillment steps.
• Action: Decompose into Marble models with event handlers; use custom PL/SQL only for helpers.

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. Marble: The New Standard for Business Logic

Marble is IFS Cloud's domain-specific language for defining data models and business logic. It replaces the need for many custom PL/SQL extensions.

What Marble Can Do

• Define custom entities (logical units) with full model semantics.
• Express attribute validations, calculated fields, and business rules declaratively.
• Define custom services (stateless operations) that can be exposed as OData endpoints.
• Create event handlers that react to data changes in the standard framework.
• Compose business logic without writing raw SQL.

Example: Custom Approval Logic

In Apps 10 (PL/SQL-heavy):

In IFS Cloud (Marble + OData):

The Marble approach separates concerns: the model knows about validation, the service handles the workflow, and listeners (via events) handle audit logging. This makes code testable and upgradeable.

2. Event Framework: Extending Without Overriding

Instead of customizing core PL/SQL, IFS Cloud applications emit events at key points (Before/After Create, Update, Delete, etc.). Custom code listens for these events and reacts.

Common Event Hooks

• Before Create: Validate input, populate defaults, block creation if conditions aren't met.
• After Create: Cascade changes to related entities, log activity, call external services.
• Before Update: Enforce business rules, prevent invalid state transitions.
• After Update: Synchronize data, trigger downstream workflows.

Example: Auto-Calculating Freight

No override of standard logic. No transaction risk. The event handler is notified, reacts, and the framework manages consistency.

3. OData Projections: Exposing Business Logic via APIs

An OData projection is a read/write data view that can combine standard IFS tables, custom entities, and calculated fields. Crucially, projections can invoke custom PL/SQL helpers.

Example: Custom Invoice Summary Projection

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

GET /odata/v4/CustomInvoiceSummary?$filter=AgeingDays gt 30

No direct database access required. The projection enforces security and consistency.

4. Custom Logical Units: Structured Customizations

For complex custom entities (not just tweaks to standard ones), define a custom logical unit. This gives you a structured model with lifecycle management.

This is exposed as an OData resource, versioned with your custom module, and upgradeable.

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):

• Create a custom CustomPricingService with a public method.
• Expose via OData projection CustomPricingCalculation.
• External systems call the OData endpoint; no direct SQL.
• Event handlers in IFS automatically react to order changes and invoke pricing recalculation.

Migration Steps:

1. Extract pure logic from the PL/SQL package (remove CRUD, transaction handling).
2. Rewrite in Marble as a stateless service.
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):

• Define state transitions and validations in Marble entity definitions.
• Create event handlers for each transition (before/after approval, before/after shipment).
• Use custom actions to expose complex workflows as OData service operations.
• Each operation is stateless; multi-step workflows are coordinated by the caller or a workflow engine.

Migration Steps:

1. Map each procedure to a discrete business event (OrderApproved, OrderShipped).
2. Create event handlers that respond to these events.
3. Wrap complex sequences as a custom OData service operation.
4. Test state transitions; handle compensating operations (reversals) explicitly.
5. Update callers to invoke the OData operations.

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 the IFS Cloud scheduler (if in a customer-managed deployment).
• Or define a custom event and trigger it on a schedule via a webhook or message queue.
• Perform the same work via OData bulk operations or data migration jobs.

Migration Steps:

1. Identify what the scheduled task does (e.g., "finalize unpaid invoices").
2. Express this as a series of OData API calls or a data migration job.
3. Register a scheduled trigger (via the IFS scheduler or an external job system).
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 Cloud Data Integration or third-party iPaaS tools (Zapier, Make, etc.) mediate connections.
• 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. Set up authentication (OAuth 2.0 or mTLS) for external systems.
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. Many teams discover that 30-40% of their custom PL/SQL is obsolete and can be retired entirely, saving months of rework.

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 data access via OData and Marble models.
• 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 for annual testing against new IFS releases.

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. Define the Data Model (Marble):

2. Implement the Business Logic Service:

3. Expose via OData:

4. Hook into Standard Workflows (Events):

Outcome:

• No direct table updates; all changes go through the OData model (audit logging automatic).
• Stateless operations; no temporary tables or session complexity.
• Testable business logic; unit tests don't need a running database.
• External systems call a stable OData endpoint; no SQL knowledge required.
• Batch jobs use data migration or scheduled service operations, not raw DBMS_SCHEDULER.

Key Takeaways

1. PL/SQL is not forbidden in IFS Cloud, but its role has shrunk from a first-class extension mechanism to a supporting player. The future is Marble, OData, and events.

2. Start with a rigorous audit: Catalog what you have, classify it, and retire what's obsolete. This single step saves weeks of rework.

3. Embrace stateless design: OData is stateless by nature. Your business logic must adapt. This is not a limitation; it's a feature that makes cloud deployments secure, scalable, and upgradeable.

4. Marble is your new language: It's not optional. The sooner you learn it, the faster your migration and the better your cloud future.

5. Events and projections replace custom hacks: In Apps 10, you overrode procedures and wrote to temporary tables. In Cloud, you listen for events and expose logic via projections. It's cleaner, more maintainable, and compatible with IFS's upgrade rhythm.

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 for rearchitecture, not just porting: A true "lift and shift" is rarely possible. Budget for 30-40% of custom code to be rewritten. The payoff is a system that lasts through multiple IFS upgrades without costly customization debt.

The migration from Apps 10 PL/SQL to IFS Cloud is a chance to clean house. Your custom code will be smaller, cleaner, and infinitely more maintainable. Take the time to do it right.