Connectivity Agent in Oracle Integration Cloud(OIC)

Creating and installing the Connectivity Agent Process in OIC 

Click Here

OIC Monitor Role KT by Vangimalla for the XXON

GM Fulfillment track 

  Batch Pick Track 

     Created Connectivity Agent and Deployed in Azure DB

  Microsoft Azure adaptor --  OIC to Azure DB Connection 

Error :JDBC Connection Errors with azure database connection 

        Sql Server JDBC Driver object has been closed 

  Raised SR with oracle and with working session  : 

  Oracle team requested Agent access logs ,Upgrade Logs and 

When was the agent was last restarted ?

 XXON Fulfilment Queue - Batch Pick Submission  is a ESS Job --8 integrations every 20 min 

         8 integration details as rows in azure DB custom table xxon_oic_queue with status as "New"

 Below scheduled for every  day 12:30 PM IST once only  -- Put on hold batch pick after 11:50 to 12:30 

 --------------------------------------------------------

      XXON Fulfilment INT49 (stand Alone ) -- insert 2 records in azure same table with status "New"  

  XXON Fulfilment INT49 (Job set ) --it will trigger integrations in OIC 

    it will have part 1(Demand file Integration ) and Part 2 (Demand file Integration )

      XXON Fulfilment Queue -  BM Job Excluding INT49 -   every day 12:30 PM IST once 

        it will insert 7 integrations in Azure Same table xxon_oic_queue

Collection Job Set -- wms check stock availability 

at 1:30 fusion BIP report (Busrsting ) Scheduled at 1:30 PM IST 

 

 

    Planned/ Not Planned  Order Details 

Interview Questions(Fusion and OIC) 2026-May

 Interview Questions(Fusion and OIC) 

1. Can you please brief me about your Oracle Fusion and OIC application career?

Answer:

Over my XX-year career in enterprise application integration, the last several years have been dedicated exclusively to the Oracle Cloud Ecosystem, specializing as a Lead Architect across Oracle Fusion Cloud (ERP, HCM, Procurement) and Oracle Integration Cloud (OIC).

My expertise spans designing real-time, event-driven integrations using REST/SOAP frameworks, orchestrating complex bulk data migrations using FBDI and BIP data extracts, and decoupling heavy legacy PL/SQL business logic from ATP databases into OIC cloud-native design patterns. 

I frequently act as the bridge between technical delivery teams and functional stakeholders, establishing end-to-end integration roadmaps, security strategies, and performance tuning for multi-tier global rollouts.

-------------------------------

2. When you will use REST and SOAP web services?

Answer:

REST APIs: 

I leverage REST for high-performance, synchronous, lightweight, and stateless record-level operations (CRUD). Because it utilizes JSON payloads, it consumes significantly less bandwidth and provides fast execution, making it the strategic choice for modern web applications, mobile interfaces, and real-time point-to-point updates (Ex:-Triggering an immediate customer update from a CRM).

SOAP Web Services: 

I default to SOAP when dealing with complex business process orchestrations or deep, heavily nested parent-child hierarchies that must be committed under strict transactional boundaries (WS-Security / ACID compliance). 

It is also essential when interfacing with legacy middleware platforms or enterprise service buses (ESBs) that strictly require formal, contract-driven WSDL agreements.

----------------------

3. Suppose, Oracle has both REST and SOAP web services available (ex: creation of Supplier or creation of Invoices). Apart from this we can also use FBDI process. So, which one you will use it on what scenario and why?

Answer:

My architectural choice is driven by Volume, Frequency, and Data Complexity:

REST API: 

I will choose REST if the source system sends single transactions or micro-batches in real time (e.g., an e-commerce platform pushing an invoice immediately upon checkout). Why: It offers instant synchronous feedback, lightweight JSON processing, and immediate error handling.

-------------------------

4. When will you go for the FBDI approach?

Answer:

I enforce the FBDI approach under two distinct scenarios:

Data Conversion/Migration Phases: Moving historical open balances, 20,000+ supplier master records, or active purchase orders from a legacy ERP system to Oracle Fusion during initial implementation go-lives.

High-Volume Inbound Batch Interfaces:

Daily or weekly scheduled bulk processing. For instance, if an external global procurement or specialized inventory system generates a daily CSV extract containing 5,000+ receiving transactions or AP invoices to be synchronized into Fusion.

SOAP Service: 

I will choose SOAP if the REST endpoint lacks functional schema parity for a highly specific sub-entity (e.g., complex banking profile assignments nested deep within a supplier profile) or if a legacy middleware system cannot parse JSON.

FBDI Process: 

I will strictly use FBDI if the data volume exceeds record-level thresholds (e.g., thousands of invoices pushed as a nightly batch file). Why: Web services will hit HTTP timeout limits or rate-limiting thresholds (typically around 5,000 calls per hour) when processing massive volumes. FBDI handles bulk imports asynchronously at the database layer via staging tables and ESS jobs.

-------------------

5. I have developed an integration and wanted to make it more secure. What are the different things that you will do to secure the integration? Tell me the answer with a real-time example.

Answer:

I apply a defense-in-depth security strategy spanning multiple layers.

Real-Time Example: 

Consuming a sensitive payroll and direct-deposit integration between an external HR system (like ADP) and Oracle HCM Cloud.

Security Actions Taken:

Network Layer: 

Configure IP Whitelisting / Allowlisting on the OIC API Gateway to drop traffic from any source outside ADP's explicit public IP blocks.

Authentication Layer: 

Decommission basic authentication and implement OAuth 2.0 Client Credentials Grant utilizing short-lived access tokens.

Authorization Layer: 

Apply the principle of least privilege; the OIC integration service user in Fusion is mapped to a custom role restricting access only to the necessary employee/bank accounts REST resources.

Payload Security: 

Enforce PGP Encryption for files sitting on intermediate staging SFTP locations.

Data Masking: 

Turn off payload tracking in production and programmatically redact/mask high-risk fields (such as SSNs or Bank Account Numbers) within the OIC error-handling logs to prevent data leaks.

--------------------

6. Explain about OAuth2.0?

Answer:

OAuth 2.0 is an industry-standard token-based authorization framework. It allows an external application to gain limited access to Oracle Fusion or OIC resources without ever exposing or hardcoding primary user passwords.

In a standard system-to-system integration, OIC uses the Client Credentials Grant type. The external client sends its secure Client ID and Client Secret to the Identity Provider (like Oracle IDCS) to retrieve a short-lived Access Token (formatted as a JSON Web Token - JWT). This token acts as a digital key valid for a limited window (typically 60 minutes) and contains restricted scopes (permissions). The client then attaches this string to the HTTP header as a Bearer Token to invoke the target APIs securely.

--------------------

7. Do you have any experience with VBCS?

Answer:

Yes, I have extensive experience leveraging Visual Builder Cloud Service (VBCS) to create custom UI extensions and composite applications that fill functional gaps in Oracle Fusion. I specialize in configuring VBCS Service Connections to consume Fusion REST APIs natively, managing business objects, mapping data flows, and implementing role-based component security directly within the custom user interfaces to enhance the overall user experience.

--------------------

8. What is the stage file operation max size?

Answer:

In Oracle Integration Cloud (OIC), the maximum size file that can be processed by the Stage File action in-memory is 50 MB.

For handling larger data files (up to 1 GB), we must utilize the Read File in Chunks option within the Stage File configuration. This streams the data sequentially using a looping background mechanism to prevent JVM out-of-memory exceptions.

-----------------------

9. Any challenges with FTP/SFTP or ATP DB adapters or Salesforce Adapter?

Answer:

Yes, throughout my delivery experience, I have managed and resolved several specific adapter limitations:

FTP/SFTP Adapter: 

Faced connection timeout issues and file locking constraints when large files were being written to concurrently. Resolved this by introducing strict file-naming conventions (.tmp shifting to .csv post-write), optimizing pool settings, and configuring explicit retry limits on the OIC connection properties.

ATP DB Adapter: 

Encountered transaction timeout thresholds during long-running PL/SQL package executions. I resolved this by decoupling heavy transactional logic, moving data processing out of database-level packages, and converting the long-running database steps into modular, asynchronous OIC SQL Lookups, or breaking the data into smaller chunks.

Salesforce Adapter: 

Dealt with schema drift issues and handling bulk API limits when processing millions of change-event records. Solved this by setting up proper custom fields mapping, relying on Salesforce's daily API limits monitoring, and structuring batch sizes effectively within the integration mapping layers.

--------------

10. I have created a scheduled integration to create invoices and want to convert it into an app-driven integration. How can we do that?

Answer: 

Since OIC does not allow a direct structural change to the integration type pattern on an existing canvas, the migration requires a precise rebuild strategy:

Export Mappings: Open the current Scheduled Integration, navigate to the main data mapping transformation node for the Invoice payload, and Export the underlying .xsl map file locally.

Create App-Driven Shell: Create a brand new integration selecting the App-Driven Orchestration design pattern.

Configure REST Trigger: Drag a REST adapter to the initial Trigger node. Configure a POST operation with a relative URI (e.g., /createInvoice), and supply a sample JSON payload representing the incoming invoice request format.

Re-create Invoke & Import Maps: Add your target Oracle ERP Cloud Invoice creation adapter node. Open the newly generated Map node, click Import, and upload the .xsl file saved in Step 1 to instantly restore the data transformation logic.

Clean up Staging Elements: If the scheduled flow used a Stage File loop to parse a bulk file, remove that structural loop since the app-driven trigger now accepts the individual transaction payloads directly in real time.

-----------------------

11. How will you identify which subject areas need to be used?

Answer:

To accurately identify the correct OTBI (Oracle Transactional Business Intelligence) Subject Area, I follow a systematic process:

Analyze the Functional Requirements: 

Map out the target transactional entities needed (eg:- Real-time Invoice validation requires Payables, while asset tracking requires Fixed Assets).

Cross-Reference Tables to Subject Areas: 

I utilize the official Oracle Cloud Subject Area to Database Table Mapping Documentation.Inspect via UI: Navigate to the specific functional page in Fusion, use the "Inspect" tool or the Help --> About This Page feature to identify the underlying View Object (VO).

OTBI Subject Area Guide: Validate the corresponding Subject Area using the Oracle BI Composer or the standard catalog tree structure (eg:- Payables Invoices - Transactions Real Time).

----------------------

12. How will you get DFF attributes in OTBI reports?

Answer:

DFF (Descriptive Flexfield) attributes do not appear in OTBI reports automatically; they must be explicitly synchronized:

Run Deployment: Ensure the DFF is properly configured and successfully deployed within the Manage Descriptive Flexfields setup task in Fusion.

BI Enabled Checkbox: Ensure that the specific DFF segments have the "BI Enabled" checkbox checked within their segment definition settings.

Run Synchronization Job: Submit the enterprise scheduled process: "Import Oracle Fusion System Management Extensibility Applications Data into BI" (commonly referred to as the BIP/OTBI Sync job). Once completed, the attributes materialize under a dedicated sub-folder labeled "Flexfields" within their respective OTBI Subject Area folder hierarchy.

--------------

13. Have you created custom roles in Oracle Fusion?

Answer:

Yes. In accordance with strict internal security compliance, we avoid assigning seeded Oracle roles directly to users. I navigate to the Security Console, find the closest standard Oracle seeded role (such as Accounts Payable Manager), and perform a Deep Copy. From there, I edit the custom role prefixing it (e.g., _AP_MANAGER) to add or remove specific functional security policies or data security policies according to business requirements.

------------------

14. Explain when we will create a data role and when we will create a job role?

Answer:

(Note: In modern Oracle Cloud releases, explicit "Data Roles" have been unified into the Role Common Model, but the structural concept remains critical).

Job Role: Created to define a business function or job description (e.g., General Accountant or Procurement Agent). It acts as a logical container for Duty Roles, specifying what actions (functions, pages, tasks, privileges) a user holding that role can execute across the system.

Data Role (Security Context Assignment): Created or assigned to control which specific data records that user can perform those job actions on. It bridges the Job Role to a concrete security context, such as a specific Business Unit (BU), Ledger, or Inventory Organization. For example, a user has the Job Role of "Accounts Payable Clerk," but their Data Context limits them to processing transactions only within the "US Business Unit.

-------------------

15 Tell some of the Supplier Manager Privileges?

Answer:

Key privileges rolled into a Supplier Manager security profile include:

PO_MANAGE_SUPPLIER_REGISTRATION_PRIV (Manage Supplier Registration)

PO_EDIT_SUPPLIER_PRIV (Edit Supplier profile details, sites, contacts)

PO_VIEW_SUPPLIER_PRIV (View Supplier profile pages)

PO_MANAGE_SUPPLIER_PROFILES_PRIV (Maintain general supplier profile structures)

---------------

16. Can you tell me any one of the complex reports that you have built?

Answer:

I designed a global Cross-Module Reconciliation Report combining data across Payables (AP), Purchasing (PO), and General Ledger (GL) using BI Publisher (BIP).

Complexity: It required stitching complex multi-table joins across AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, PO_HEADERS_ALL, and GL_JE_LINES to trace mismatched multi-currency variances.

Performance Optimization: Because the report targeted millions of records across multi-national ledgers, standard OTBI timed out. I wrote optimized SQL queries using explicit hints, drove filtering criteria efficiently via standard bind parameters, structured the data model as an optimized parent-child XML data dataset, and created a highly formatted Excel template layout tailored for corporate finance auditors.

-------------------

17. Have you done the callback integration?

Answer:

Yes. Callback integrations are standard in my bulk integration designs involving asynchronous file exports or imports. For example, when invoking the ERP Integration Service to run an FBDI file upload, the process is completely asynchronous. Instead of forcing OIC to resource-intensively loop and poll the Oracle Fusion ESS job status every few minutes, I configure the call payload with a Callback URL. Once the Fusion bulk import job completes, Oracle Fusion natively invokes the OIC callback endpoint, passing the final execution status (SUCCESS/ERROR) along with the log summary files for automated processing.

--------------------------

18. Any extension or customization you did?

Answer:

I have delivered multiple functional extensions. A primary example involved extending the Oracle Fusion Procurement cloud page where users needed to run real-time credit checks on high-value vendors. Since this logic didn't exist natively, I configured a custom action button on the Fusion Page using Page Composer, which triggered a secure external REST service call managed by OIC to pull data from a third-party credit rating agency and present the metric on-screen inside a custom descriptive flexfield (DFF).

--------------------------

19. I want to hide a column. I have 5 columns total: For US client show only 3 DFF attributes. For Europe Client show 2 DFF attributes only. How to achieve this task?

Answer: 

This conditional visibility requirement is achieved via Page Composer utilizing EL (Expression Language) Expressions:

Open the target transactional screen in Oracle Fusion and initiate a Sandbox containing the Page Composer tool.

Select the specific DFF column component you want to hide/show and open its Component Properties.

Locate the "Rendered" or "Visible" attribute property box, click the dropdown, and select Expression Builder.

Write an EL Expression that dynamically reads the current user’s security profile context or the session Business Unit attribute.

Example EL Expression: #{bindings.BusinessUnit.inputValue == 'US_BU'}

Apply the inverse logic expression to the European columns. This ensures that when the page renders, the framework checks the runtime transaction context and visually displays or completely hides the respective DFF columns on the client browser dynamically.

-------------------

20. Have you created any drill-down reports for dashboards?

Answer:

Yes, I routinely build interactive analytical dashboards in OTBI. I configure drill-down interactions by building a high-level summary report (e.g., Total Spend by Business Unit chart) and a secondary, granular detailed report (Line-Level Invoice Details). Inside the summary report’s column properties, under the Interaction tab, I define the action link to "Navigate to BI Content" pointing directly to the detailed report. I pass the shared context columns (like Business Unit ID or Fiscal Year) as dynamic prompt parameters so that clicking an element on the dashboard instantly refines and expands into the deep-dive line reports.

-----------------

21. I wanted to see sales order reports or finance reports based on the role?

Answer:

This is managed securely at the BI Catalog Security Folder Level:

In the BI catalog (/shared/Custom), structure your folders by functional domain (e.g., create a /Sales Reports folder and a /Finance Reports folder).

Click on the Permissions setting for the specific folder.

Strip out general public access and add explicit Application Roles matching your user profiles (e.g., assign access to the /Sales Reports folder exclusively to the Customer Service Representative application role, and assign /Finance Reports strictly to the Financial Analyst application role).

This ensures that when a user logs into the BI reporting environment, they are granted a clean, secure view containing only the folders and reporting dashboards mapped to their authorized organizational functions.

------------------

22. Any tasks comes to you. How will you estimate tasks?

Answer:

As a Lead Technical Consultant, I enforce a standardized Estimation Framework based on complexity tiers rather than guessing:

Requirement Discovery & Scoping: Breaking down the target object into one of three complexity tiers:

Simple: Straightforward point-to-point pass-through API with minimal data transformation mapping. (Estimated: 3-5 days).

Medium: Multiple endpoints, conditional data routing, or standard DFF mappings requiring custom lookups. (Estimated: 8-12 days).

Complex: High-volume FBDI pipelines involving complex file chunking, custom error-handling callbacks, or heavy BIP data transformations. (Estimated: XX-25+ days).

Buffer Allocation: Every single estimate accounts for standard lifecycle phases: Design & Mapping (25%), Development & Unit Testing (40%), Functional / UAT Defect Support (20%), and Deployment Preparation (15%).

------------------------------

23. What are the modules that you have worked on?

Answer:

Throughout my integration portfolio, I have heavily supported across three primary Oracle Fusion Cloud modules:

Oracle Financials (ERP): Payables (AP Invoices, Payments), Receivables (AR Customers, Billing), General Ledger (Journal Imports).

Oracle Procurement: Purchasing (PO Creation), Supplier Model (Supplier Profiles, Sites, Bank Accounts).

Oracle Human Capital Management (HCM): Global Human Resources (Worker Onboarding, Assignments), Payroll Interfaces (Extracts, Bank Account updates).

---------------------

24. How will make Functional team properly communicate with Technical team if we hire as Lead Technical Role? 

Answer:

With XX years of industry experience, I have observed that communication breakdowns happen because Functional teams speak in business processes (eg:-'Three-way matching failed'), while Technical teams speak in schemas and code (e.g.,'ORA-00904 invalid identifier').To bridge this gap effectively as a Technical Lead, I will implement a structured integration delivery process:

Mandate Functional Design Documents (FDD) or (FSD): 

I will require the functional team to deliver a formal FDD outlining the complete business logic, source/target field names, and crisp error validation conditions before code construction begins.

Enforce Technical Design Document (TDD or TSD) Walkthroughs: 

I will lead collaborative mapping workshops where our technical developers present their integration mapping logic back to the functional consultants using plain business language to validate rules early.

Standardize an Integration Mapping Matrix: Establish a shared spreadsheet defining clear Source Field --> Transformation Rule --> Target Oracle API Attribute columns. This serves as the single source of truth that aligns both teams, eliminating assumption errors and significantly reducing cycle times during User Acceptance Testing (UAT).

Usage of For-Each and Switch Action Logics in Oracle Integration Cloud

 

For-Each and Switch Action Logics

 

We will choose REST Connection

Choose POST method

Choose Sample JASON

Inline: Provide Request Payload

And then provide the Response Payload in the next

 

For storing the count of integers, we will create variables using Assign action

Add For-Each and insert the repeating element

 

Inside loop add the condition using switch action

It means positive integer

We have to assign action to pdate the positive integer

positiveCount+1

Value <0 then it is negative

 

 

 

Now, it is in configure status

Activate integration

Run integration

 

Testing:

 

 

Logic Actions: For-Each, Parallel, Switch,Scope and While in Oracle Integration Cloud

 

Mastering Logic Actions in Oracle Integration Cloud

 

v Oracle Integration Cloud (OIC) provides powerful Logic Actions that help developers design complex integration flows without heavy coding.

v These actions control execution, looping, branching, and grouping of steps in an integration.

 

1.       For-Each

 

Ø  Iterates over a list of items (e.g., repeating for each record in a payload).

Ø  Useful for batch processing, like looping through employee records.

Ø  Runs sequentially — one item at a time.

2.Parallel

Ø  Executes multiple branches simultaneously.

Ø  Ideal for scenarios where independent tasks can run in parallel (e.g., sending notifications + updating DB).

Ø  Improves performance by reducing wait time.

 

3.Scope

Ø  Groups actions together into a logical unit.

Ø  Helps with error handling and transaction management.

Ø  Example: Wrap multiple DB calls in a scope to handle rollback if one fails.

 

4.Switch

Ø  Implements conditional branching (like IF-ELSE).

Ø  Executes different flows based on conditions (e.g., order type = online vs offline).

Ø  Simplifies decision-making logic.

 

5.While

Ø  Loops until a condition is met.

Ø  Example: Retry until a service responds successfully.

Ø  Must be used carefully to avoid infinite loops.

 

 

Why Logic Actions Matter

ü  They reduce coding effort with visual design.

ü  Enable complex workflows like approvals, retries, and parallel processing.

ü  Make integrations scalable and maintainable.

Within OIC, navigate to power symbol on the right side of the page

 

 

 

 

 

 

 

 

 

 

 

 

Logic Actions in OIC — When to Use Them

Action	Execution Type	Best Use Case	Example Scenario
For‑Each	Sequential	Process list items	Loop through invoices
Parallel	Simultaneous	Run independent tasks	Notify + update DB
Scope	Grouped	Error handling	Rollback on failure
Switch	Conditional	Branch logic	Online vs offline orders
While	Iterative	Retry until success	Poll service until response

 

Real-Time Used Cases:

🔸For‑Each

Scenario: Client want to automate Supplier Invoice Processing

• ERP system sends a batch of supplier orders.
• OIC uses For‑Each to loop through each order.
• For every order: validate data, check inventory, calculate invoice, and create invoice record.
• Benefit: Ensures each order is processed independently without affecting others.

⚡ Parallel

Scenario: Client want to automate Customer Order Fulfillment

• When a customer places an order, OIC triggers two tasks simultaneously:
• Send confirmation email to the customer.
• Update the order status in the database.
• Benefit: Saves time by running independent tasks together, improving responsiveness.

📦 Scope

Scenario: Client want to automate Discount & Invoice Generation

• OIC groups actions like “Apply Discount” and “Create Invoice” inside a Scope.
• If any step fails, the error handler inside Scope manages rollback or sends alerts.
• Benefit: Centralized error handling ensures transactional consistency.

🔀 Switch

Scenario: Client want to automate Payment Routing

• OIC evaluates the Order_Type field.
• If Online, route to payment gateway API.
• If Manual, generate invoice for offline billing.
• Else, follow default path (log error, notify admin).
• Benefit: Dynamic routing based on business rules without separate integrations.

🔄 While

Scenario: Automate Payment Confirmation Retry

• OIC checks if Payment_Status = Confirmed.
• If not, the While loop retries payment confirmation until success or timeout.
• Benefit: Ensures reliable payment processing with retry logic.

 

Integration Flow for Parallel function:

Conclusion:

Together, these five logic actions form the backbone of intelligent automation in Oracle Integration Cloud. They transform complex, error‑prone business processes into scalable, resilient, and adaptive workflows.

By combining iteration, parallelism, error management, conditional routing, and retry logic, OIC empowers enterprises to achieve true end‑to‑end digital integration.

It reduces manual effort, accelerating operations, and ensuring business continuity.

OIC Logic Actions are the architecture of modern, automated business flows.

 

Sales Order Comprehensive Line Export in Oracle Fusion

 Sales Order Comprehensive Line Export

SELECT DISTINCT bu.bu_name                                   AS
                "Business Unit Name",
                Trunc(ha.ordered_date)                       AS
                "Sales Ordered Date",
                party.party_name                             AS "Customer Name",
                bill_party.party_name                        AS
                "Bill-to Customer Name",
                bill_acc.account_number                      AS
                "Bill-to Account Number",
                ship_party.party_name                        AS
                "Ship-to Customer Name",
                hl.address1
                || ', '
                || hl.city                                   AS
                "Ship-to Address",
                ha.order_type_code                           AS "Order Type",
                msi.item_number                              AS "Item Number",
                msi.item_number                              AS "Item Name",
                msi.description                              AS
                "Item Description",
                dla.status_code                              AS "Status",
                dla.ordered_qty                              AS "Quantity",
                dla.ordered_uom                              AS "UOM",
                dla.unit_selling_price                       AS "Item Price",
                ship_site.party_site_number                  AS "Ship To Site",
                ( dla.ordered_qty * dla.unit_selling_price ) AS "Amount",
                -- Billing Frequency (usually stored at fulfillment level for recurring)
                (SELECT periodicity_code
                 FROM   doo_billing_plans
                 WHERE  fulfill_line_id = dfa.fulfill_line_id
                        AND ROWNUM = 1)                      AS
                "Billing Frequency",
                -- Number of Billing Periods
                (SELECT billing_num_of_periods
                 FROM   doo_billing_plans
                 WHERE  fulfill_line_id = dfa.fulfill_line_id
                        AND ROWNUM = 1)                      AS
                "Number of Billing Periods",
                dla.line_type_code                           AS "Line Type",
                dla.canceled_flag                            AS
                "Cancel Backorders",
                dla.open_flag                                AS
                "Enforce Single Shipment",
                dla.line_number
FROM   doo_headers_all ha,
       doo_lines_all dla,
       doo_fulfill_lines_all dfa,
       fun_all_business_units_v bu,
       hz_parties party,
       (SELECT header_id,
               Max(cust_acct_id) AS cust_acct_id
        FROM   doo_order_addresses
        WHERE  address_use_type = 'BILL_TO'
        GROUP  BY header_id) bill_addr_fix,
       (SELECT header_id,
               Max(party_id)      AS party_id,
               Max(party_site_id) AS party_site_id
        FROM   doo_order_addresses
        WHERE  address_use_type = 'SHIP_TO'
        GROUP  BY header_id) ship_addr_fix,
       hz_cust_accounts bill_acc,
       hz_parties bill_party,
       hz_parties ship_party,
       hz_party_sites ship_site,
       hz_locations hl,
       egp_system_items_vl msi
WHERE
  -- Primary Joins
  ha.header_id = dla.header_id
  AND dla.line_id = dfa.line_id
  AND ha.org_id = bu.bu_id
  AND ha.sold_to_party_id = party.party_id
  AND dla.inventory_item_id = msi.inventory_item_id
  AND dla.inventory_organization_id = msi.organization_id
  -- Bill-to Joins
  AND ha.header_id = bill_addr_fix.header_id (+)
  AND bill_addr_fix.cust_acct_id = bill_acc.cust_account_id (+)
  AND bill_acc.party_id = bill_party.party_id (+)
  --Ship-to Joins
  AND ha.header_id = ship_addr_fix.header_id (+)
  AND ship_addr_fix.party_id = ship_party.party_id (+)
  AND ship_addr_fix.party_site_id = ship_site.party_site_id (+)
  AND ship_site.location_id = hl.location_id (+)
  -- Parameters
  AND ha.order_number = '98514'
--AND ha.order_number = :p_order_number
--AND bu.bu_name = :p_bu_name
ORDER  BY dla.line_number ASC