Source File Validation Using Stage File and Throw New Fault in OIC
Use Case
In Oracle Integration Cloud (OIC), validating the source file before processing ensures data accuracy and integrity. This implementation:
- Reads the file using the Stage File action.
- Validates the data within the Stage Mapper using the defined schema.
- If validation errors exist, it throws a fault using the Throw New Fault activity.
Solution Steps
Step 1: Configure the Stage File Action
- Add a Stage File action to the integration.
- Select Read File and choose an appropriate read mode (e.g., "Read Entire File" or "Read in Chunks").
- Define the schema to structure the file content.
Step 2: Implement Validation in Stage Mapper
- Open the Stage Mapper and apply validation rules on fields (e.g., mandatory checks, format validation, length constraints).
- Create a Message Count Variable to track the number of validation errors.
- If a validation error is found, increment the message count and populate the
Messagefield in the validation schema.
Example Validation Schema:
{
"Validation": [
{
"Message": "Error description"
}
]
}
Step 3: Throw a Custom Fault if Errors Exist
- Add an If condition after the Stage Mapper:
- Condition: Message Count > 0
- If the condition is met:
- Use Throw New Fault activity.
- Pass the validation messages in the fault payload.
Example Fault Response:
{
"Validation": [
{
"Message": "Missing required field: Employee ID"
},
{
"Message": "Invalid date format: Start Date"
}
]
}Detailed screenshots:Integration flow snap:
Read source file using nxsd:
Read raw file - one line as a column:
Write file using stage and validation json sample
Mapping:
Throw new fault:
Few of the validations conditions:
1. 1st record should start from 5310:substring($ReadRawFile/nsmpr0:ReadResponse/ns22:FileRowSet/ns22:FileRow[position() = 1.0]/ns22:FileData, 1, 4 ) != "5310"
2. Check file structure - one file header exists, one file trailer exists, atleast one batch header and batch trailer abd detail record exist, number of batch trailer = number of batch header.
((count($ReadSourceFile/nsmpr1:ReadResponse/ns:Payments/ns25:FileHeader) != 1) or ((count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:FileTrailer) != 1) or ((count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:BatchHeader) = 0) or ((count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:Details) = 0) or ((count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:BatchTrailer) = 0) or (count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:BatchHeader) != count($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:BatchTrailer))))))
3.for 1st time run, business date shouls match with the system date.
((/nssrcmpr:execute/ns21:request-wrapper/ns21:ProcessRequest/ns21:IsReprocess != "Y") and ($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:FileHeader/ns25:ValueDate != xp20:format-dateTime(fn:adjust-dateTime-to-timezone(fn:current-dateTime(), xsd:dayTimeDuration("PT8H")), "[Y0001][M01][D01] ")))
4.2nd record should start with RH.
substring($ReadRawSourceFile/nsmpr0:ReadResponse/ns23:FileRowSet/ns23:FileRow[position() = 2.0]/ns23:FileData, 1, 2 ) != "RH"
5. Value date is in YYYYMMDD format.
((/nssrcmpr:execute/ns21:request-wrapper/ns21:ProcessRequest/ns21:IsReprocess != "Y") and (ns25:ValueDate != xp20:format-dateTime(fn:adjust-dateTime-to-timezone(fn:current-dateTime(), xsd:dayTimeDuration("PT8H")), "[Y0001][M01][D01] ")))
6. Store number should have 004 0r 024 only + store number should be non numeric.
not(contains("004,024", ns25:StoreNumber)) or (string(number($ReadSourceFile/nsmpr1:ReadResponse/ns25:Payments/ns25:BatchHeader/ns25:StoreNumber)) = "NaN")
7. Input time ahould be in hhmmss format.
xp20:format-dateTime(concat("2025-01-01T", substring(ns25:InputTime, 1, 2), ":", substring(ns25:InputTime, 3, 2)), "[Y0001][M01][D01][H01][m01]") != concat("20250101", ns25:InputTime)
8.validate if RT record is followed by D1 record.
If substring(ns23:FileData, 1,2) = "RT"If substring ($ReadRawSourceFile/nsmp0:ReadResponse/ns23:FileRowSet/ns23:FileRow[ns23:FileData = current()/ns23:FileData]/preceding-sibling::ns23:FileRow[1.0]/ns23:FileData, 1, 2 ) != “D1”
9. Validate if last -1 record should be RT
substring ($ReadRawSourceFile/nsmp0:ReadResponse/ns23:FileRowSet/ns23:FileRow[(position() = (last() - 1.0))]/ns23:FileData, 1, 2 ) != "RT"
10. Validate if batch trailer amount are non numeric value.
((string(number(oraext:create-delimited-string($ReadSourceFile/nsmp1:ReadResponse/ns25:Payments/ns25:BatchTrailer/ns25:TotalAmountoftheApprovedDebits, ","))) != "NaN") and (string(number(oraext:create-delimited-string($ReadSourceFile/nsmp1:ReadResponse/ns25:Payments/ns25:Details[(ns25:TransactionStatus = "A")]/ns25:TransactionAmount, ","))) != "NaN"))
11. Sum of batch trailer amount equal to sum of all detail amount
sum($ReadSourceFile/nsmp1:ReadResponse/ns25:Payments/ns25:BatchTrailer/ns25:TotalAmountoftheApprovedDebits) != sum($ReadSourceFile/nsmp1:ReadResponse/ns25:Payments/ns25:Details[(ns25:TransactionStatus = "A")]/ns25:TransactionAmount)
