OIC HCM - How to extract data from Oracle Cloud HCM using OIC | FlowActionsService | Calling SOAP service to run an HCM Extract

Usecase: 

Here, we will extract the data from HCM and then download the data from UCM uaing Flow Actions Service and Generic Soap Service

To know how to create HCM extract based on user entity, follow my below blogs:

https://soalicious.blogspot.com/2024/05/oic-hcm-configure-hcm-extract-bulk-data.html

Required WSDLs:

Extract data:

https://hostname:443/hcmService/FlowActionsService?WSDL

Upload or download From UCM:

https://hostname:443/idcws/GenericSoapPort?WSDL

Implementation steps:

1. Create a HCM extract based on employee user entity.
2. FlowActionsService - Extract data from HCM which will be uploaded to UCM using below operation
1. SubmitFlow
1. Flow name
2. Parameter values like param: Effective Date
3. FlowInstanceName
4. Legislative Data group name
5. Schedule Date
6. Recurring Flag : false
3. Take a while loop and continue till getFlowTaskInstanceStatus = COMPLETED
1. Add wait 5 mins
2. FlowActionsService - getFlowTaskInstanceStatus
1. Flow Instance Name
2. Flow Task Instance Name
3. Legislative data group Name
3. Take am assign and update the getFlowTaskInstanceStatus
4. FlowActionsService - getIntegrationContentId
1. Flow Instance name
2. Flow task instance name
3. Legislative data group name
4. Integration name
5. Take an assign and create query text value using content id as concat("dDocName <substring> `",upper-case(ContentId),"`")
6. GenericSoapService - get dID from content id
1. wbKey="CS"
2. IdService="GET_SEARCH_RESULTS"
3. Document field name="QueryText"
4. Value = $varQueryTextValue (step5)
7. GenericSoapService - get File from UCM from dID
1. webKey = "CS"
2. IdService ="GET_FILE"
3. Document Field name="dID"
4. Value =dID fetched from step6
8. Decode the data : decodeBase64(Contents)

Notes:

1. Flow name : hcm Extract name
2. Integration name : the integration name what we provided during extract creation
3. Flow instance name = should be unique like flow name + guid
4. Flow task instance name = hcm extract name

Detailed screenshots:

Create soap connections:

FlowActionsService:

Generic SOAP service for UCM upload / download:

Integration flow:

Assign required data to variables:

Submit Flow

While loop

Wait 5 mins

GetFlowTaskInstanceStatus

Update flowTaskInstanceStatus

GetIntegrationContentID

Assign content id

Get dID from content id

Get file from UCM using dID

Decode the data

Test and Monitoring:

OIC activity stream payloads

UCM 

Check data from HCM Extract:

Reference:

https://tangenz.com/exporting-data-from-oracle-hcm-cloud-using-oic-tangenz-corporation/

https://myoraclejourney.com/2023/01/06/calling-soap-service-to-run-an-hcm-extract/

ODI - How to find a special chars from csv .gz file

Usecase: 

We have a BICC extract which stores the data .csv.gz file into the object storage and then get the file via odi to insert to adw temporary table but its failing as it has special chars in the file. So here we will see the steps how to find and remove the special chars and then process it.

Error details:

Solution steps:

Step1) From the failed scenario >> open session >> steps>> expand all >> right click on "Insert New Rows Custom IKM Oracle Control Append >> open aession task >> code >> Fetch C$ table from section

Step2) run the below command in db to get the error details:

BEGIN

DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE(

Table_name => 'C$_0Ansnsmssksk22_EXT);

END;

Step3) check the below tables to get bad records details:

Select * from Validate$62_Log

Select * from validate$62_BAD

Step4) copy the bad records details to notepad ++ and see the bad characters 

https://soalicious.blogspot.com/2024/07/notpad-how-to-find-special-characters.html

Step5) if bad record or special character exists then we have to remove it following the below steps :

##Go to object storage bucket path via terminal here, oci object storage buckets is mounted to unix or linux terminal.

/home/oracle/bucket-bicc-prod

##To see the .csv.gz file

ls -ltr

##Take a back up of the file

Cp file_name ../ODI_Backup

##Unzip file

gzip -d file_name.csv.gz

##Open the file using vi and search with "/ then bad record few words and enter

It will show places in highlighted. 

Esc >> press i and remove special chars hidden as double space.

Save it with :wq

##Zip the file

gzip filename

##Run the insert code with load id

Once it is success, commit and verify the row counts wih the file count with below command

zcat filename | wc -l

##Move the .csv.gz file to archive folder

mv filename ../bucket-archive-prod

Step6) mark the failed scenario as success and restart the load plan

OIC - Error occured in fetching the attachment for Email

Usecase: There is one integration where we are retrieving object from oci object storage and getting a streamReference and from another integration we are calling  this 1st integeation and receiving the stream reference and assigning back to attachment to Email notification and we are getting below error:

Error occured in fetching the  attachment for Email 

Solution :

In the called intnegration: use encodeReferenceToBase64() function to encode the stream reference to Base64 data

In the caller integration: use decodeBase64ToReference() function to decode it back to reference and use it to email attachment and it will work.