Processing HCM Extract data file with OIC ( Oracle Integration Cloud)

 Recently we have use case where customer would like to 

1. Extract Time Card data from HCM
2. Addition data from some other HCM tables
3. Load data in ADW 
4. Validate and Merge data from HCM extract as well data from other HCM tables
5. Generate a new extract ( combination of Step#1 and 2) 
6. FTP file to customer .

And to implement this , I have designed a simple OIC flow . This OIC flow is scheduled flow and on execution

1. Validate if HCM extract files is available in UCM or not , if available then Read HCM files , process the data from HCM extract file and load into a database table (table1) in ADW, if no HCM extract file available in UCM server not then stop any further processing
2. Trigger a BIP report
3. Read data from BIP report and load into Table ( Table2) in ADW
4. Call DB api over DB adapter connection, validate data from Table1 , table2. Combined data from these 2 tables
5. Call SFTP adapter and load data into SFTP server,
6. Archive HCM extract file  

Below is very High level Process flow

for additional details feel free to reach me at inquiry@bizinsightinc.com.