ApplyReceiptService using SOAP UI in Oracle Integration Cloud

 Step-by-Step: Apply Receipt via SOAP UI in OIC

========================================================

To apply a receipt amount using SOAP UI in Oracle Integration Cloud (OIC), you must invoke the ApplyReceiptService SOAP operation with the correct payload structure and authentication. This complements your REST-based receipt creation and ensures the receipt is applied to invoices in Oracle Receivables Cloud.

1. Locate the WSDL

Use the ApplyReceiptService WSDL:

https://fusionURL/finReceivablesReceiptsService/ApplyReceiptService?WSDL

Import into SOAP UI or OIC SOAP Adapter.

2. Understand Required Fields

Typical payload includes:

ReceiptNumber: From REST API response.

ApplicationAmount: Amount to apply.

InvoiceNumber: Target invoice.

CustomerAccountNumber (optional but recommended).

Sample Request Payload 1:

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

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"

                  xmlns:typ="http://xmlns.oracle.com/apps/financials/receivables/receipts/receiptsService/types/">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:applyReceipt>

         <typ:receiptNumber>RCPT12345</typ:receiptNumber>

         <typ:applicationDetails>

            <typ:applicationAmount>1000</typ:applicationAmount>

            <typ:invoiceNumber>INV56789</typ:invoiceNumber>

         </typ:applicationDetails>

      </typ:applyReceipt>

   </soapenv:Body>

</soapenv:Envelope>

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

Sample Request Payload 2

Request Payload

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

 <soap:Body>

 <ns1:createAndApplyReceiptAsync

xmlns:ns1="http://xmlns.oracle.com/apps/financials/receivables/receipts/s

hared/createAndApplyReceiptService/commonService/types/">

 <ns1:createAndApplyReceipt

xmlns:ns2="http://xmlns.oracle.com/apps/financials/receivables/receipts/s

hared/createAndApplyReceiptService/commonService/">

 <ns2:BusinessUnitName> Vision Operations</ ns2:BusinessUnitName>

 <ns2:ReceiptMethodName>Check - BofA-ED</ns2:ReceiptMethodName>

 <ns2:ReceiptNumber>Rec_1163</ns2:ReceiptNumber>

 <ns1:CurrencyCode>USD</ns1:CurrencyCode>

 <ns2:Amount>362.80</ns2:Amount>

 <ns2:CustomerName>AR_Customer</ns2:CustomerName>

 <ns2:CustomerAccountNumber>1001</ns2:CustomerAccountNumber>

 <ns2:TransactionNumber>1163</ns2:TransactionNumber>

 </ns1:createAndApplyReceipt>

 </ns1:createAndApplyReceiptAsync>

 </soap:Body>

</soap:Envelope>

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

Sample Response Payload 2

The Response payload will contain the receipt number if the invocation is

successful.

<ns0:createAndApplyReceiptAsyncResponse xmlns=""

xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"

xmlns:ns0="http://xmlns.oracle.com/apps/financials/receivables/receipts/s

hared/createAndApplyReceiptService/commonService/types/"

xmlns:wsa="http://www.w3.org/2005/08/addressing"

xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wsswssecurity-utility-1.0.xsd">

<ns2:result xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/"

xmlns:ns1="http://xmlns.oracle.com/apps/financials/receivables/receipts/s

hared/createAndApplyReceiptService/commonService/"

xmlns:ns2="http://xmlns.oracle.com/apps/financials/receivables/receipts/s

hared/createAndApplyReceiptService/commonService/types/"

xmlns:tns="http://xmlns.oracle.com/adf/svc/errors/"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:type="ns1:CreateAndApplyReceiptResult"><ns1:Value>

 <ns2:BusinessUnitName> Vision Operations</ ns2:BusinessUnitName>

<ns1:ReceiptMethodName> Check - BofA-ED</ns1:ReceiptMethodName>

<ns1:ReceiptNumber>Rec_1163</ns1:ReceiptNumber>

<ns1:ReceiptDate/><ns1:GlDate xsi:nil="true"/>

<ns1:Amount>435.35</ns1:Amount>

<ns1:CustomerName>AR_Customer</ns1:CustomerName>

<ns1:CustomerAccountNumber>10011</ns1:CustomerAccountNumber>

<ns1:CurrencyCode>USD</ns1:CurrencyCode>

<ns1:TransactionNumber>1163</ns1:TransactionNumber>

</ns1:Value>

</ns2:result>

</ns0:createAndApplyReceiptAsyncResponse>

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

4. Authentication

Use Basic Auth with Fusion credentials.

Ensure the user has Receivables Manager or equivalent role.

5. Test in SOAP UI

Send the request and validate the response.

Check Fusion UI: Receivables → Manage Receipts → Search by Receipt Number.

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

Automate in Oracle Integration Cloud

====================================

1. Use SOAP Adapter.

2. Configure with the WSDL.

3. Choose applyReceipt operation.

4. Map fields from REST response (Ex:-ReceiptNumber, Amount, InvoiceNumber).

Integration Flow

Trigger: REST receipt creation.

Stage Variables: Capture receipt details.

Invoke SOAP Adapter: Apply receipt.

Error Handling: Log faults (Ex:-invalid invoice, insufficient amount).

Audit Trail: Store response in ATP DB or log file.

Demo Tables in Oracle SQL

 Employee Table Creation and Insertion Scripts in Oracle SQL

===========================================================

CREATE TABLE EMP

       (

EMPNO NUMBER(4) NOT NULL,

        ENAME VARCHAR2(10),

        JOB VARCHAR2(9),

        MGR NUMBER(4),

        HIREDATE DATE,

        SAL NUMBER(7, 2),

        COMM NUMBER(7, 2),

        DEPTNO NUMBER(2));

INSERT ALL

  INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800,  NULL, 20)

  INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300,  30)

  INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500,  30)

  INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('02-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20)

  INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30)

  INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('01-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30)

  INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('09-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10)

  INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20)

  INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10)

  INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('08-SEP-1981', 'DD-MON-YYYY'), 1500, 0,    30)

  INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20)

  INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('03-DEC-1981', 'DD-MON-YYYY'), 950,  NULL, 30)

  INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('03-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20)

  INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10)

SELECT * FROM DUAL;

Department Table Creation and Insertion Scripts in Oracle SQL

===========================================================

CREATE TABLE DEPT

       (DEPTNO NUMBER(2),

        DNAME VARCHAR2(14),

        LOC VARCHAR2(13) );

INSERT ALL

  INTO DEPT (DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'NEW YORK')

  INTO DEPT (DEPTNO, DNAME, LOC) VALUES (20, 'RESEARCH',   'DALLAS')

  INTO DEPT (DEPTNO, DNAME, LOC) VALUES (30, 'SALES',      'CHICAGO')

  INTO DEPT (DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIONS', 'BOSTON')

SELECT * FROM DUAL;

--4 rows inserted

select *from DEPT;

LOCATIONS  table creation and insertion script in ORacle SQL

=====================================================

CREATE TABLE LOCATIONS (

    LOCID      NUMBER(5) PRIMARY KEY,

    CITY       VARCHAR2(50),

    STATE      VARCHAR2(50),

    COUNTRY_ID NUMBER(5)

);

INSERT ALL 

    INTO LOCATIONS (LOCID, CITY, STATE, COUNTRY_ID) VALUES (1, 'BOSTON', 'BOSTON', 11)

    INTO LOCATIONS (LOCID, CITY, STATE, COUNTRY_ID) VALUES (2, 'DALLAS', 'DALLAS', 22)

    INTO LOCATIONS (LOCID, CITY, STATE, COUNTRY_ID) VALUES (3, 'CHICAGO', 'CHICAGO', 33)

SELECT * FROM DUAL;

Salary Grade Table Creation and Insertion Scripts in Oracle SQL

===========================================================

CREATE TABLE SALGRADE

        (GRADE NUMBER,

         LOSAL NUMBER,

         HISAL NUMBER);

 

INSERT INTO SALGRADE VALUES (1,  700, 1200);

INSERT INTO SALGRADE VALUES (2, 1201, 1400);

INSERT INTO SALGRADE VALUES (3, 1401, 2000);

INSERT INTO SALGRADE VALUES (4, 2001, 3000);

INSERT INTO SALGRADE VALUES (5, 3001, 9999);

Oracle to Cash in Oracle Fusion

 Order to Cash (O2C) functional flow and the underlying Fusion tables

1. Order Entry and Booking

Functional Flow: 

A customer places an order. 

The order is entered into the system, validated, and booked. 

Booking action triggers the orchestration process to fulfill the order.

Key Technical Tables:

DOO_HEADERS_ALL (Order Header details)

DOO_LINES_ALL (Order Line details)

DOO_FULFILL_LINES_ALL (Orchestration/Fulfillment details)

2. Pick Release

Functional Flow: The system checks inventory for the item and reserves it. Pick slips are generated for the warehouse staff to pick the items from the shelves and move them to the staging area.

Key Technical Tables:

WSH_DELIVERY_DETAILS (Delivery line information, status changes to 'Released to Warehouse')

WSH_NEW_DELIVERIES (Delivery grouping)

INV_RESERVATIONS (Inventory reservation details)

3.Ship Confirm 

Functional Flow: The warehouse confirms the delivery is loaded and shipped. The delivery is closed, inventory is decremented, and the system prepares the data to be sent to Receivables for invoicing and Costing for COGS.

Key Technical Tables:

WSH_DELIVERY_DETAILS: The delivery lines (Status changes to 'Shipped').

WSH_DELIVERY_ASSIGNMENTS: The crucial mapping table that links the delivery lines (DELIVERY_DETAIL_ID) to the actual delivery header (DELIVERY_ID).

WSH_NEW_DELIVERIES: The delivery header itself (Status updates to 'Closed' or 'In-Transit').

INV_MATERIAL_TXNS: Records the actual deduction of inventory (the material issue transaction).

4. Invoicing (AutoInvoice)

Functional Flow: The fulfillment system sends data to Accounts Receivable (AR). 

The "Import AutoInvoice" scheduled process runs, converting the shipment data into a finalized customer invoice.

Key Technical Tables:

RA_INTERFACE_LINES_ALL (Staging table for AutoInvoice)

RA_CUSTOMER_TRX_ALL (Invoice Header)

RA_CUSTOMER_TRX_LINES_ALL (Invoice Lines)

AR_PAYMENT_SCHEDULES_ALL (Tracks what is due and when)

5. Receipt Creation and Application

Functional Flow: The customer pays the invoice. The payment (receipt) is entered into AR and applied against the open invoice, closing the customer's balance.

Key Technical Tables:

AR_CASH_RECEIPTS_ALL (Receipt header information)

AR_RECEIVABLE_APPLICATIONS_ALL (Links the receipt to the specific invoice)

6.Transfer to General Ledger (GL)

Functional Flow: The subledger accounting (SLA) entries generated from shipping (COGS), invoicing (Revenue/Receivables), and receipts (Cash/Receivables) are transferred and posted to the General Ledger.

Key Technical Tables:

XLA_AE_HEADERS & XLA_AE_LINES (Subledger Accounting entries)

GL_JE_HEADERS & GL_JE_LINES (Final General Ledger journal entries)

About Natural Account in Oracle Fusion

 

 

About Natural Account in Oracle Fusion

(Documented by Venkat)

******************************************************

How is the Natural Account identified and mapped in the Oracle Fusion GL Table?

 Step 1: A journal entry is created using a code combination.

Step 2: The combination includes multiple segments (Company, Department, Natural Account, etc.).

Step 3: The Accounting Flexfield Definition identifies the Natural Account segment (e.g., SEGMENT3).

Step 4: That segment value (e.g., 5100) determines the account type (e.g., Expense)

 

 

 

GL_CODE_COMBINATIONS Table

                                          Holds up to 30 segment columns (SEGMENT1 to SEGMENT30).
                                 One of these segments is designated as the Natural Account.

Accounting Flexfield Definition

v Defines which segment corresponds to the Natural Account.

Example: SEGMENT3 might be labeled as Natural Account in the above pic.

 

Mapping Process

We check the Flexfield Setup to see which segment is assigned.

That segment in the GL combinations table holds the Natural Account values (like 5100 for “Expenses”).

GL_CODE_COMBINATIONS → Accounting Flexfield Definition → Natural Account Segment

What is Account Type?

Each Natural Account is assigned an Account Type (A, L, E, R, or X).

It is critical because it determines how Oracle performs Year-End Closing.

Revenue/Expense accounts are zeroed out to Retained Earnings.

Asset/Liability/Equity accounts carry their balances forward.

What is Account Validation?

It usually has a Value Set attached to it to ensure only predefined, valid account numbers are used in the system.

Why is the account called "Natural"?

It is called "natural" because it follows the logical, inherent classification of accounting.

Regardless of which department spends the money, the "natural" classification of a "Laptop Purchase" remains an "Asset" or "Equipment Expense."

   

Real-Time Requirement:-

Q: Write a SQL query to  fetch Natural Account, account_type, currency, and invoice amount details

SELECT

    gcc.segment1 AS natural_account,

    gcc.account_type,

    ai.invoice_currency_code,

    SUM(ail.amount) AS total_invoice_amount

FROM

    ap_invoices_all ai,

    ap_invoice_lines_all ail,

    ap_invoice_distributions_all aid,

    xla_distribution_links xdl,

    xla_ae_lines xal,

    gl_code_combinations gcc

WHERE

    ai.invoice_id = ail.invoice_id

    AND ail.invoice_id = aid.invoice_id

    AND ail.line_number = aid.invoice_line_number

    AND aid.invoice_distribution_id = xdl.source_distribution_id_num_1

   

AND xdl.source_distribution_type = 'AP_INV_DIST'

    AND xdl.ae_header_id = xal.ae_header_id

   

AND xdl.ae_line_num = xal.ae_line_num

    AND xal.code_combination_id = gcc.code_combination_id

    AND ai.cancelled_date IS NULL

GROUP BY

    gcc.segment1,

    gcc.account_type,

    ai.invoice_currency_code

ORDER BY

    total_invoice_amount DESC 

FAQ

Q: If a journal entry fails due to an invalid Natural Account, how would you troubleshoot? 

A: Check the chart of accounts setup, validate the Natural Account segment value, and ensure it is enabled and assigned to the correct account type.

Q: How would you design a Chart of Accounts for a multinational company? 

A: Include segments like Company, Department, Natural Account, and Location. Ensure Natural Account is standardized across entities for consolidated reporting.

Q:Can you give an example of a Natural Account value? 

A:Example: 5100 – Office Supplies Expense (Expense account).

Q: How does the Natural Account interact with Subledger Accounting (SLA)? 

A: SLA rules map transactions from subledgers (AP, AR, FA) to the Natural Account segment in GL.

Q: How do BI Publisher or OTBI reports use the Natural Account? 

A:They filter and group financial data based on Natural Account values for reporting.

Q: What controls can be applied to Natural Accounts? 

A: Cross-validation rules, segment value security, and account hierarchies.

 

The ORA-12505 error in Oracle SQL Developer means

 The ORA-12505 error in Oracle SQL Developer

Root cause of the problem: 

The most common cause is that the Oracle Database instance has not been started or is not registered with the listener. This generally happens when the computer has been shut down or SQL Developer has not been used for a long time. 

Steps to Resolve the problem:

Step 1: Open Command Prompt as Administrator

To ensure you have the necessary permissions to manage database services, you must run the Command Prompt with elevated privileges.

• Press the Windows Key, type cmd.

• Right-click on Command Prompt and select Run as administrator.

Step 2: Launch SQL*Plus without Logging In

Once the terminal is open, you need to enter the SQL*Plus environment without immediately connecting to a specific database schema.

• Type the following command and press Enter: sqlplus /nolog

Step 3: Connect as a System Administrator (SYSDBA)

Next, you need to connect to the Oracle instance with administrative rights to change its status.

• Type the following command: conn sys as sysdba;

• When prompted for a password, type the password (e.g., admin) and press Enter.

  Note: For security reasons, the characters will not appear on the screen as you type the password.

If the database is down, you will see a message stating: "Connected to an idle instance."

Step 4: Start the Oracle Instance

Finally, you need to move the database from an idle state to an open state so that SQL Developer can connect to it.

• Type the following command and press Enter: startup

Wait a few moments for the process to complete. You will see several lines of data regarding the System Global Area (SGA), followed by the confirmation message: "ORACLE instance started."

---

Conclusion

Database is now open and mounted! You can now return to SQL Developer and connect to your database as usual. If you frequently encounter this issue, ensure that your Oracle services are set to "Automatic" in the Windows Services panel.

Check the following for execution process: