Working...
Overview
In many enterprise payment integrations, payment files generated from Oracle ERP are transmitted to banking partners such as HSBC for payment execution. After processing the payment file, HSBC generates an acknowledgement (ACK) file that must be retrieved and loaded back into Oracle ERP for reconciliation and audit purposes.
This blog describes an OIC-based solution that automates the end-to-end retrieval and processing of HSBC payment acknowledgement files.
Business Requirement
When Oracle ERP sends a payment file to HSBC, the treasury team requires confirmation that the file has been successfully received and processed by the bank.
The solution should:
Retrieve the payment reference number from ERP.
Call HSBC APIs using the reference number.
Download the payment acknowledgement file.
Upload the acknowledgement file into ERP UCM.
Submit an ESS job to import/process the file.
Validate successful processing.
Re-query ERP to confirm the final acknowledgement status.
Solution Architecture
High-Level Flow
OIC Scheduler
|
V
Call ERP BIP Report
|
V
Get Payment Reference Number
|
V
Call HSBC Acknowledgement API
|
V
Download ACK File
|
V
Upload File to ERP UCM
|
V
Submit ESS Job
|
V
Monitor ESS Job Status
|
V
Call ERP BIP Report Again
|
V
Validate Final Processing Status
Integration Design
Step 1: Scheduled Integration Trigger
A scheduled OIC integration runs periodically to identify payment files awaiting acknowledgement processing.
The scheduler frequency can be configured based on business requirements.
Step 2: Retrieve Payment Reference from ERP
The integration invokes a BI Publisher (BIP) report in Oracle ERP.
The report returns:
Payment Process Request (PPR)
Payment Batch Details
Payment Reference Number
Bank Reference Information
The reference number returned by the report is used as the primary identifier for HSBC API calls.
Step 3: Invoke HSBC Acknowledgement API
Using the payment reference number, OIC invokes the HSBC acknowledgement API.
Sample request:
{
"paymentReference": "REF123456789"
}
HSBC returns:
Processing status
Acknowledgement file
Reference details
Response codes
Step 4: Download Acknowledgement File
If the acknowledgement is available, OIC retrieves the ACK file from HSBC.
Depending on HSBC implementation, the file may be:
XML
CSV
TXT
CAMT format
The file content is stored temporarily within the integration flow.
Step 5: Upload File to ERP UCM
The acknowledgement file is uploaded to Oracle ERP Universal Content Management (UCM).
Typical upload details:
Account: fin$/payables/import$
Document Name
Security Group
Content Type
The UCM document ID is captured for downstream processing.
Step 6: Submit ESS Job
After successful upload, OIC invokes the ERP ESS web service to process the acknowledgement file.
Typical ESS parameters include:
UCM Document ID
File Name
Import Process Name
The ESS Job ID is captured for monitoring.
Step 7: Monitor ESS Job Status
OIC continuously polls the ESS job status until one of the following conditions is reached:
SUCCEEDED
ERROR
WARNING
This can be implemented using:
Wait activity
Scope
While loop
Retry framework
Step 8: Re-Validate Using BIP Report
Once the ESS job completes successfully, OIC invokes the BIP report again.
This validation step confirms:
Acknowledgement processed successfully
Payment status updated
ERP records reconciled correctly
The report serves as a final verification layer before completing the integration.
Error Handling
The integration includes robust exception handling for:
HSBC API Failures
Authentication issues
Network failures
Invalid references
Timeout errors
UCM Upload Failures
Invalid content
UCM connectivity issues
Security permission failures
ESS Processing Failures
Import validation errors
Incorrect file format
ERP processing exceptions
Notifications can be sent through email or monitoring dashboards for failed transactions.
Benefits
End-to-End Automation
Eliminates manual retrieval and upload of acknowledgement files.
Improved Reconciliation
Ensures ERP reflects the actual bank processing status.
Faster Processing
Acknowledgements are processed automatically and available to finance teams sooner.
Enhanced Auditability
Provides complete traceability from payment initiation to acknowledgement confirmation.
Conclusion
This OIC integration automates the complete HSBC payment acknowledgement lifecycle by combining Oracle ERP BI Publisher reports, HSBC APIs, UCM file uploads, and ESS job processing. The additional validation step using a second BIP report ensures that acknowledgement files are successfully processed and reflected in ERP, providing a reliable and auditable payment reconciliation process.
No comments:
Post a Comment