AG Pending Interface Transactions Report

 AG Pending Interface Transactions Report

SELECT Al1.Period_Name,

       Al1.Batch_Number,

       Al1.Group_Id,

       Al1.Batch_Creation_Date,

       Al1.Batch_Created_Name,

       Al1.From_Company_Cd,

       Al1.To_Company_Cd,

       Al1.Transaction_Type,

       Al1.Init_Or_Recvr,

       Al1.Trx_Number,

       Al1.Account_Combination,

       Al1.Currency_Code,

       Al1.Initiator_Amt_Dr,

       Al1.Initiator_Amt_Cr,

       Al1.Line_Amount_Dr,

       Al1.Line_Amount_Cr,

       Al1.Line_Description,

       Al1.Batch_Reject_Reason,

       Al1.Header_Reject_Reason,

       Al1.Line_Reject_Reason,

       Al1.Trx_Id,

       Al1.Batch_Id

  FROM (SELECT Fbtc.Batch_Number Batch_Number,

               Fhdr.Trx_Number Trx_Number,

               Fdst.Dist_Number Dist_Line,

               Perd.Period_Name Period_Name,

               Fbtc.Batch_Date Batch_Date,

               Fbtc.Creation_Date Batch_Creation_Date,

               Fbtc.Last_Update_Date Batch_Last_Updt_Date,

               Usbc.User_ID Batch_Created_Id,

               Usbc.Username Batch_Created_Name,

               Usbl.User_Id Batch_Last_Updt_Id,

               Usbl.Username Batch_Last_Updt_Name,

               Fbtc.Gl_Date Gl_Date,

               Fbtc.Source SOURCE,

               Substr(Fbtc.Initiator_Name, 1, 3) From_Company_Cd,

               Substr(Fhdr.Recipient_Name, 1, 3) To_Company_Cd,

               Fbtc.Initiator_Name Initiator_Company_Name,

               Fhdr.Recipient_Name Recipient_Company_Name,

               Fbtc.Description Batch_Description,

               Fhdr.Description Header_Description,

               Fdst.Description Line_Description,

               Fbtc.Trx_Type_Name Transaction_Type,

               Fdst.Party_Type_Flag Init_Or_Recvr,

               Fbtc.Currency_Code Currency_Code,

               Fdst.Amount_Dr Line_Amount_Dr,

               Fdst.Amount_Cr Line_Amount_Cr,

               Glcc.Segment1 || '.' || Glcc.Segment2 || '.' || Glcc.Segment3 || '.' ||

               Glcc.Segment4 || '.' || Glcc.Segment5 || '.' || Glcc.Segment6 || '.' ||

               Glcc.Segment7 || '.' || Glcc.Segment8 Account_Combination,

               Glcc.Segment1 Company,

               Glcc.Segment2 Glbl_Acct,

               Glcc.Segment3 Lcl_Acct,

               Glcc.Segment4 Dept,

               Glcc.Segment5 Intco,

               Glcc.Segment6 Project,

               Glcc.Segment7 Bu,

               Glcc.Segment8 Future,

               Fbtc.Control_Total Batch_Control_Total,

               Fbtc.Running_Total_Dr Batch_Total_Dr,

               Fbtc.Running_Total_Cr Batch_Total_Cr,

               Fhdr.Init_Amount_Dr Initiator_Amt_Dr,

               Fhdr.Init_Amount_Cr Initiator_Amt_Cr,

               Fbtc.Import_Status_Code Import_Status_Code,

               Frjb.Batch_Reason Batch_Reject_Reason,

               Frjh.Header_Reason Header_Reject_Reason,

               Frjl.Line_Reason Line_Reject_Reason,

               Fbtc.Batch_Id Batch_Id,

               Fbtc.Group_Id Group_Id,

               Fhdr.Trx_Id Trx_Id,

               Fdst.Dist_Id Dist_Id,

               Fdst.Ccid Code_Combination_Id

          FROM Fun_Interface_Batches Fbtc,

               Fun_Interface_Headers Fhdr,

               Fun_Interface_Dist_Lines Fdst,

               Per_Users Usbc,

               Per_Users Usbl,

               Gl_Code_Combinations Glcc,

               Gl_Periods Perd,

               (SELECT Batch_Id, Reject_Reason Batch_Reason

                  FROM Fun_Interface_Rejections

                 WHERE Trx_Id IS NULL

                   AND Dist_Id IS NULL) Frjb,

               (SELECT Trx_Id, Reject_Reason Header_Reason

                  FROM Fun_Interface_Rejections

                 WHERE Trx_Id IS NOT NULL

                   AND Dist_Id IS NULL) Frjh,

               (SELECT Dist_Id, Reject_Reason Line_Reason

                  FROM Fun_Interface_Rejections

                 WHERE Dist_Id IS NOT NULL) Frjl

         WHERE Fhdr.Batch_Id(+) = Fbtc.Batch_Id

           AND Fdst.Trx_Id(+) = Fhdr.Trx_Id

           AND Usbc.Username = Fbtc.Created_By

           AND Usbl.Username = Fbtc.Last_Updated_By

           AND Glcc.Code_Combination_Id(+) = Fdst.Ccid

           AND Perd.Period_Set_Name = '4-4-5'

           AND Fbtc.Gl_Date BETWEEN Perd.Start_Date AND Perd.End_Date

            AND Perd.Period_Type like  'MONTH%'

           AND Frjb.Batch_Id(+) = Fbtc.Batch_Id

           AND Frjh.Trx_Id(+) = Fhdr.Trx_Id

           AND Frjl.Dist_Id(+) = Fdst.Dist_Id) Al1

           WHERE 1=1

    AND Al1.From_Company_Cd= nvl(:p_company_code,Al1.From_Company_Cd)

AND Al1.Period_Name= nvl(:p_period_name,Al1.Period_Name)

File Transfer Integration

 File Transfer Integration

 File Transfer Integration

[App A (WMS System) -> generating files -> SFTP Server 1 (Src) (rbstg.files.com) ] => With in Client network

Build a File Transfer Service to transfer files from SFTP Server 1 to SFTP Server 2

[SFTP Server 2 (Target) (rbstg.exavault.com) -> App B (Third Party System)] => outside of client network

MFT - Managed File Transfer

1) List the files in SFTP Server 1 (src)

2) Loop thru the files

for-each loop

3) Read the file from SFTP Server 1 - Source

4) Write the file to SFTP Server 2  - Target - items1.csv - PGP Encrypt - items1.csv + .pgp 

5) Move (Archive) the file in SFTP Server 1 - Source

End loop

SFTP client/tool - Winscp (DONT USE), Filezilla etc.

Key Name   - a2cf58pgpkey

Passphrase - a2cf58pgpkey

SFTP: Only use FileZilla (no WINSCP)

----

Host: 130.35.100.211

Port: 5022

User: oic.user1

pwd: ZsjK40LGe4j1

/home/users/a2cfoic.user1/a2cf/a2cf57/filetransferservice/input

items*.csv

MFT - Managed File Transfer - No longer supported by Oracle

FTP/SFTP -> FTP Adapter

a2cf24pgpkey

items*.csv

for i in (select item_number, from items)

loop

i.item_number

end loop

txt

csv

dat

05th - 5:15 AM, 10:15 AM, 3:15 PM, 8:15 PM

15th - 5:15 AM, 10:15 AM, 3:15 PM, 8:15 PM

23rd - 5:15 AM, 10:15 AM, 3:15 PM, 8:15 PM

FREQ=MONTHLY;BYMONTHDAY=5,15,23;BYHOUR=5,10,15,20;BYMINUTE=15;

FREQ=MONTHLY;BYMONTHDAY=5,15,23;BYHOUR=5,10,15,20;BYMINUTE=15;

FREQ=MINUTELY;INTERVAL=5;

Java Script

 Custom Java Script in oic --Scinarios 

these will be available under functions>>User Defined

1. Http links -remove junk charecters
2. String Operations 
3. Sequence Generator to assign unique values 
4. Dateformat generation like microseconds /Timezones
5. JWT Tokens(JawaWebTokens) 
6. base64 encode for token generation 

XSLT Functions

 Advanced XSLT functions 

flow control

1.  Choose 
2.  for each
3.  for-each-group
4.  if
5.  otherwise
6.  when

Output

Build SOAP Service in OIC

    

    

 

Two types of WSDL:

Steps

Soap Connection Security :

Username Password token

OAuth 2.O

Security Assertion Markup Language -SAML

Basic Authentication 

OIC Integration : Supplier : Supplier Interface from Cloud to EBS : PLSQL Concurrent Program

 Int : Supplier : Supplier Interface from Cloud to EBS : PLSQL : Concurrent Program 

Difference between synchronous and asynchronous in oic

 Difference between synchronous and asynchronous in oic

In Oracle Integration Cloud (OIC), the terms synchronous and asynchronous refer to the communication pattern between systems or within integrations — specifically, how the sender and receiver interact in terms of waiting for a response.

🧠 Quick Mnemonic:

• Synchronous = "Stop and wait"   5 min (300 sec)

• Asynchronous = "Send and go" 6 hrs async with schedule 

🔄 Synchronous Integration

📌 Definition:

A synchronous integration waits for a response after sending a request. The client (caller) is blocked until the OIC integration finishes processing and returns a result.

✅ Key Characteristics:

• Real-time interaction

• Immediate response required

• Integration returns data or a status to the requester

• Timeouts are important to manage

• Often used in API-like interactions (e.g., REST/SOAP)

📦 Example:

An external system calls a REST-triggered integration in OIC to validate a customer. The integration calls Oracle ERP, gets the validation result, and returns it immediately to the caller.

---

🔁 Asynchronous Integration

📌 Definition:

An asynchronous integration does not wait for a response. The caller sends a request and continues without waiting for the process to complete.

✅ Key Characteristics:

• Fire-and-forget behavior

• No immediate response; may use callbacks, queues, or notifications if needed

• Suitable for long-running or batch processes

• Common in event-driven or scheduled flows

📦 Example:

An ERP system sends a file to OIC. OIC picks up the file (via File/FTP trigger), processes the data, and stores it in a database — no response is sent back to ERP.

---

🔁 Comparison Table

Feature	Synchronous	Asynchronous
Communication style	Request-Response	Fire-and-Forget
Caller waits for reply	Yes	No
Response	Required (status or data)	Optional (via callback or notification)
Best for	Real-time APIs, UI-driven requests	Background jobs, long processes
Complexity	Simple	Can involve callback or correlation
Error handling	Handled inline in response	Often handled via logging or alerting

---

🔧 Real-world Example in OIC:

Scenario	Type
REST API call that returns order status	Synchronous
Scheduled job that loads 10,000 records to ERP	Asynchronous

Connection Types Invoke and Trigger

Connection Types Invoke and Trigger

In Oracle Integration Cloud (OIC), the terms "trigger" and "invoke" refer to how integrations are initiated and how they interact with external systems. Here’s a clear comparison of the two:

Mnemonic means a special word used to help a person remember something

• Trigger = “How it starts”

• Invoke = “Who it talks to”

🔹 Trigger in OIC Definition:

A trigger is the entry point of an integration. It defines how an integration is started—usually by an event or incoming request from an external system or application.

Common Trigger Types:

• HTTP Trigger / REST Adapter – An HTTP request initiates the integration.

• SOAP Adapter – A SOAP request triggers the integration.

• Scheduled Trigger – Integration is triggered based on a time schedule (e.g., every hour).

• Event-based Triggers – For example, receiving a file, or a message on a queue.

Example Use Case:

A REST API call from an external system (like Salesforce) triggers an integration in OIC to process data and store it in Oracle ERP.

🔹 Invoke in OIC Definition:

An invoke is used to call an external system or service from within an integration flow. It's an outbound call to perform some action or retrieve data.

Common Invoke Examples:

• Calling a REST API of another system.

• Sending data to a database.

• Invoking a SOAP web service.

• Pushing data to Oracle SaaS or on-premise systems.

Example Use Case:

An integration fetches employee data from Oracle HCM using an HCM adapter invoke after being triggered by a scheduled job.

Feature	Trigger	Invoke
Purpose	Starts an integration	Calls an external system or service
Direction	Inbound (into OIC)	Outbound (from OIC to another system)
Adapter Type	Trigger-type Adapters	Invoke-type Adapters
Examples	REST trigger, File trigger, Schedule	REST invoke, SOAP invoke, DB invoke
When Used	At the start of an integration	Within an integration (after trigger)

How to overcome BIP Report Payload limitation for data larger than 10 MB

 This blog covers the size payload limitation while calling BIP report from Oracle Integration cloud. When you’re calling BIP report via Public Report Service, you will not be able to download Payload more than 10 MB of size. Click Here 

Let’s understand the above with a Business Scenario: 

 You have a large government council, and they are posting data from different modules like GL, AP, AR, Payroll to SLA (Subledger Accounting). Millions of journal lines are being posted every day and some of the information including the Chart of Accounts must be sent across Third-party supplier for their reconciliation. As a part of the activity, we need to fetch all the million lines from SLA tables to supply back to Third-party Systems. In this scenario, your data volume is far higher than 10 MB and you will often see payload limitation errors. 

There are couple of options which you can use to overcome this limitation: 

1. Create ESS Job which will burst your output to SFTP location and read data from SFTP in segment from your Integration. 

2. Use Content Manager (UCM) as a delivery option for your BIP output and read data in chunk from UCM using ReportFileID. 

In this article we’re going to use second option as it’s quite easy to implement and manage. 

Prerequisite: 

1. Please refer my earlier blog which will describe “How to call BIP report in Oracle Integration Cloud”. 

1. Update BIP Report Mapping 

Update BIP Report Mapping and provide sizeOfDataChunkDownload as 0 instead of -1. 

If you’re passing sizeOfDataChunkDownload as -1 it will return all the data back to the client, to avoid this scenario pass 0 instead of -1 it will generate the output file in UCM server and will return the report ID to fetch data in chunks. 

callBIPReport response will return Report File ID using which you can download data in chunk from UCM. 

Assign reportFileID to some variable for future use and create two more variable  

1. BeginIndex with value 0 

2. ReportDownloadDataChunk with value ‘’(blank String). 

3. reportFileID : callBIPReport à runReportResponse à reportFileID 

Now you have assigned all the parameters with appropriate value.  

In next step we will call downloadReportDataChunk operation from PublicReportWSSService to download data in chunk. 

2. Call downloadReportDataChunk Operation 

We need to call this operation until there is no data remaining to fetch. To implement this logic, we need to call this operation into the while loop, which will execute until begin index is -1. 

• Implement While loop in Integration 

Once you click on Create button, it will open mapping page where you need to provide exit criteria for While loop in New Condition section. 

Condition : BeginIndex != -1 

Note: You have defined BeginIndex in Step 1. 

Click on Validate and Save your Integration. 

• Call downloadReportDataChunk operation in Loop. 

Invoke BIP Report Connection created in “How to call BIP report in Oracle Integration Cloud” Step 1. 

• It will open on wizard. Enter the operation name, select Next.  From the next screen select Operation : downloadReportDataChunk, select Next. From the Next screen Configure Headers (Non-Mandatory) and finish the wizard. 

• Open the mapper and map the request parameters. 

• fileID: Report File ID generated by runReport operation. 

• BeginIndex: It defines from where to start reading. Ideally it should be from 0. 
• Size: It defines the size of data chunk to be downloaded in single call. It takes value in Kilo Bytes. 

• downloadReportDataChunk operation will return reportDataOffSet and reportDataChunk. 

reportDataOffSet : It will define the last index fetched from BIP report data 

reportDataChunk: It fetches the report data in encoded format 

• Assign Report Data and Report Offset to variable. 

While assigning Report Data chunk use concat function to append the data in ReportDownloadDataChunk parameter created in Step 1 and set Begin Index as reportDataOffSet. 

• Every time While loop iterate it will check for BeginIndex, if there is no data to be fetch, downloadReportDataChunk operation will return reportDataOffSet as -1 which will satisfy loop condition and it will end the iteration. 

• Now you have a final data in ReportDownloadDataChunk variable in encoded format. To decode the, create assign activity, add variable and use decodeBase64 function. 

• Click validate and save your integration. Now you can activate and test your integration. 
  This is how your Integration flow will look alike. 

BIP TO ATP Through OIC

BIP TO ATP DB Through OIC

Connections Required

v  SOAP Connection – External Report Web Service (WSS)

v  ATP DB Connection

Process Flow

step-1: Creating Integration for Running BIP Report

step-2: Create an Integration using Scheduled Orchestration                                                                                    

  

Ø  After selecting the schedule orchestration have to provide appropriate name then click on create.

step-3: Use the SOAP Connection to connect with the External Report WSS Service

step-4: Select the "Run Report" operation to call BIP Report

step-5: Configuring the Mapper here we need to pass information which necessary to call BIP  

Ø  Attribute Format: Defines the output format of the report.(XML\CSV)

Ø  Size of Data Chunk Download: Set to `-1` to retrieve the complete report data.

Ø  Report Absolute Path: Provide the exact path of the report along with the `.xdo` extension.

 

step-6: Establish an Stage File action [To convert Base64 data{Report Bytes} into Redable format]

Ø  Use “Write File” operation

Ø  Specify the file name and output directory for temporary storage

Ø  Select XSD Document {To convert data into redable format}

Ø  Upload Opaque Schema  by downloading below link           

save below content as OpaqueSchema.xsd file 

<?xml version = '1.0' encoding = 'UTF-8'?>  

<schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/"

            xmlns="http://www.w3.org/2001/XMLSchema" >

      <element name="opaqueElement" type="base64Binary" />

    </schema>

Ø  automatically it will select the schema element

 

step-7: Again need to establish an Stage File action [To specify the particular structure to the file]

 

Ø  Use “Read Entire File” operation

Ø  Specify the file name and output directory {what ever we provided in the previous stage file action}

Ø  Choose Sample delimited document (e.g. CSV)

Ø  Here we need to choose which file we need to convert into redable format

Ø  Upload exported CSV File

Ø  As well as provide Record Name and Recordset Name

step-8: Configuring the mapper

Ø  Draw map between Report Bytes(Source) To Opaque schema(Target)

step-9: Use ATP connection [Autonomous Transaction Processing] to insert data into custom ATP tables

 

Ø  Select Perform an Operation On a Table[Based on requirement it will be change] among below options 

1.      Invoke a stored procedure 
2.      Run a SQL Statement 
3.      Perform an Operation On a Table
4.      Seselet AI for SQL 

Ø  Choose Insert To insert data into custom ATP tables 

Provide the table name what we gave in the SQL Developer [Name is casesensitive]

Ø  Select schema ADMIN [ It will be based on your connection details]

step-10: Configuring the mapper

   

step-11 : Provide business identifiers then save and active the integration

 

Step-12:run integration

and check the inserted data in the oracle SQL developer