Oracle R12 Project Interface & APIs

In Oracle Projects (PA) R12, there are multiple ways to interface project-related data from external systems (like legacy systems, third-party tools, or spreadsheets). The method you choose depends on the type of data, volume, and level of control you need.

1.    Open Interface Tables (Standard Way) 

        Oracle provides interface tables for different types of project-related data. You insert data into                these tables, then run Oracle’s concurrent programs to import the data.

        Common Interface Tables:         


         Best for: Medium to large data loads
         Requires: Validation of setup values (status, types, orgs)

2.    Public APIs (PL/SQL-based)

        Oracle provides PL/SQL APIs (packages and procedures) for programmatic insertion/update of            data. These are well-validated and useful for integration with custom apps.

        Common APIs:       

       Purpose                                                          API Package
       Create Project/ Task                      PA_PROJECT_PUB.CREATE_PROJECT
       Create Agreement                         PA_AGREEMENT_PUB.CREATE_AGREEMENT
       Create Event                             PA_EVENT_PUB.CREATE_EVENT
       Import Transactions                       PA_TRX_IMPORT_PUB.CREATE_TXN

PA_PROJECT_PUB
    1. CREATE_PROJECT
    2. DELETE_PROJECT
    3. ADD_TASK
    4. DELETE_TASK
    5. UPDATE_TASK
    6. ADD_COST_CODE_TO_TASK
    7. DELETE_COST_CODE_FROM_TASK
PA_PROJECT_ASSETS_PUB  
    1. ADD_PROJECT_ASSET
    2. UPDATE_PROJECT_ASSET
    3. DELETE_PROJECT_ASSET
    4. ADD_ASSET_ASSIGNMENT
PA_EVENT_PUB
PA_DELIVERABLE_PUB

        Best for: Real-time, controlled integration (e.g., via middleware)
        Supports rollback and exception handling

3.    Web Services (SOA / REST / SOAP)

      If you're on R12.2.x, you can expose some Oracle EBS functionality as web services, or use Oracle Integration Cloud (OIC) to push data from external systems.

    •  Business Events + BPEL: Can be used to trigger imports
    •  REST/SOAP services (limited availability in EBS)
    •  Custom Web Services built on top of APIs
     Best for: Real-time integrations, external apps
     Middleware (OIC, Mulesoft, Dell Boomi, etc.) often required

4.    Interface via Excel (WebADI / FBDI-like Tools)
    •   Use WebADI to upload project or expenditure data via Excel templates.
    •   Can customize templates or create loaders.
    •   Works well for business users.

        Best for: Ad-hoc or small volume uploads
        Business-friendly but not fully automated

5.    Custom Loaders + Control Tables

      Build your own custom solution:

    •    Staging tables → Validate → Load via APIs or Interface Tables
    •    Add retry/error tracking logic
6.    Security and Setup Considerations
        Make sure all users have appropriate PA roles and responsibilities
            Validate:
    •  Project types, statuses
    •  Task and organization hierarchy
    •  Customer accounts (TCA)
    •   Expenditure types/categories/classes

    7.    Choosing the Right Option            

        NeedBest                                                         Option
        High-volume batch import                                Interface Tables
        Real-time integration                                        APIs or Web Services
        Business-user friendly upload                           WebADI
        Controlled programmatic loading                    PL/SQL APIs
        External system sync                                        Middleware + Web Services or APIs

Comments

Post a Comment

Popular posts from this blog

Oracle APPS Creating Buy One Get One Free Modifier

Image
1.    Via Oracle Pricing Manager Responsibility 2.    Navigate > Price List > Price List Setup     Verify items (buy item and get item) are on the price list.     3.    Navigate > Modifiers > Modifiers Setup    At the header level create a Modifier Type of Promotion   4.    Modifier line info should be setup as follows:   Level   - Line Modifier Type   - Promotional Goods Automatic   - Checked Pricing Phase   - List Line Adjustment   Product Attribute - Item Number   Product Attribute value   - the item that the customer orders   Volume type   - Item Quantity                       Break type   - Recurring     Operator   - Between UOM   - select the appropriate unit of measure Value from ...
Read more

SQL query to find pending PO for approval in Oracle EBS R12

SELECT   fu.user_name po_created_by,pha.org_id ,          (SELECT NAME || ' ( ' || short_code || ' )'             FROM hr_operating_units            WHERE organization_id = pha.org_id) ou_name,          pha.segment1 po_num, pha.revision_num, SUM(pla.quantity) po_qty, SUM(pla.unit_price) po_price,          pha.authorization_status, pha.creation_date po_creation_date,          wn.to_user, wn.subject, wn.original_recipient, wn.begin_date,          TRUNC (SYSDATE - wn.begin_date) no_days_pending,d.name Approval_path          ,wn.from_role forrwad_by,wn.notification_id,pha.po_header_id     FROM wf_notifications wn,          po_headers_all pha,          fnd_user fu,          po_lines_all pla, ...
Read more