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.

Oracle fusion ERP - Multi file bursting part2 | Send Multiple files by using Bursting

Usecase: 

Here, will create one data set and create 2 reports using rtf anf xsl templates and then will use 2 bursrting query using union all and Email Delivery channel.

Detailed screenshots:

Create data model >> data set using sql query.

Create email bursting

Create 2 reports pdf and text using rtf and xsl templates.

Create ess job

Run ess job from scheduled processes

Oracle fusion ERP - Multi file bursting | Send Multiple files by using Bursting

Usecase: Here we will split the data block using vendor name as key and send the multiple files using Email delivery channel.

Used Data model SQL query:

SELECT B.VENDOR_NAME, B.VENDOR_ID,

A.INVOICE_ID,

A.INVOICE_NUM,

A.INVOICE_AMOUNT,

A.AMOUNT_PAID,

A.INVOICE_DATE,

A.DESCRIPTION,

A.BATCH_ID,

A.BASE_AMOUNT

FROM AP_INVOICES_ALL A, POZ_SUPPLIERS_V B WHERE A.VENDOR_ID =B.VENDOR_ID AND A.VENDOR_ID IN (300000047414635,300000047414571 3000000047414503,300000047414679)

Used Bursting Query:

Select Vendor_name KEY, -- split by --

'MultiReport1_Temp' TEMPLATE, -- TEMPLATE NAME OR REPORT NAME --

'en-US- LOCALE, -- English Language--

'PDF' OUTPUT_FORMAT, -- PDF, EXCEL,RTF,CSV,HTML,XLS--

VENDOR_ID||'.pdf' OUTPUT_NAME, -- Output File Name --

'EMAIL' DEL_CHANNEL, -- MAIL,FAX,FILE,FTP,PRINT,SFTP --

'Test@test.com' PARAMETER1, -- To mail.--

'Oracle@oracle.com' PARAMETER3 -- FROM MAIL --

'Supplier Invoice Details ' PARAMETER4, -- MAIL SUBJECT --

'Please find attached document for Invoice Details' PARAMETER5, --MAIL BODY--

'true' PARAMETER6 -- MAIL HAVING ATTACHMENT OR NOT--

FROM(

SELECT DISTINCT B.VENDOR_NAME, B.VENDOR_ID FROM AP_INVOICES_ALL A, POZ_SUPPLIERS_V B WHERE A.VENDOR_ID =B.VENDOR_ID AND A.VENDOR_ID IN (300000047414635,300000047414571 3000000047414503,300000047414679)

)

Detailed screenshots:

OIC - Append operation in Data stitch action | Incrementally build a messags payload using Data stitch

Usecase: Here, we will see how to perform append operation using Data stitch action.

We will feed array of ids, for each id, call a another service and check the id exist value true or false in a scope and assign the values to a global oblect variable response item and finally append the item to response array object. Next we will get all the id responses outside of the scope.

Why data stitch:

1. We can incrementally build a message payload from one or more existing  payloads with the stitch.
2. Supports both partial and full replacement of the message payload.
3. Supports both scalar and complex type variables

Implementation steps:

1. Create a rest trigger and create an appdriven orchatration integration and configure  rest request - response payload
2. Take a for each loop and iterates for each data - Id
3. Take a scope
4. Call integration action or rest call to get the details for the give id.
5. Create two golbal variables
1.  One for response Array which will have the array of Id responses
2. One for response Item which will have one id item response
6. Take a stitch action and perform the following
1. Assign the response id to response_item id
2. Assign the response Exists value to Response_item exists
3. Append the response_item result to response_array result.
7. Outside of the scope, map the global responae_array data to final rest response.

Detailed steps with snaps:

Oracle Fusion ERP webservices and operations

Urls:

1. SAAS Home Page :  https://hostname:443/
2. SAAS UCM : https://hostname:443/cs
3. BIP URL  :  https://hostname:443/xmlpserver or analytics
4. BIP Web Service  :  
• https://<fusion app url>/xmlpserver/services/ExternalReportWSSService?wsdl
• https://<fusion app url>/xmlpserver/services/PublicReportWSSService?wsdl
• https://<fusion app url>/xmlpserver/services/ScheduleReportWSSService?wsdl
•  Absolute Path :  Provide complete xdo path
•  Example : /Custom/Financials/int/Reports/samplereport.xdo
5. ERP Adapter :
• Service Catalog :
• https://<fusion app url>/fndAppCoreServices/ServiceCatalogService?wsdl
• Erp Integration :
• https://<fusion app url>/fscmService/ErpIntegrationService
• Event Catalog :
• https://<fusion app url>/soa-infra
• Event Configuration 
• https://<fusion app url>/soa-infra/PublicEvent/catalog
• Event Subscriber Check:
• https://<fusion app url>/soa-infra/PublicEvent/subscriptions
6. Token Relay
• https://<fusion app url>/fscmRestApi/tokenrelay
7. Attachment Service 
• https://<host>.fa.ocs.oraclecloud.com/publicFinancialCommonErpIntegration/ErpObjectAttachmentService?wsdl

HCM Details:

1. You encrypt files using the Oracle HCM Cloud public key.
2. The data-loading process decrypts files using the Oracle HCM Cloud private key.
3. Generate the PGP encryption key pair and download the Oracle HCM Cloud public key to Encrypt the file.  

Submit HCM Data Load

Upload File to UCM using following UCM Service. 

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

• Read the file as an attachment
• Pass the opaque contents to UCM
• Get the content Id from the response.
• Service IdcService="CHECKIN_UNIVERSAL"
• dDocTitle
• dDocAccount
• dDocType
• dSecurityGroup
• dDocAuthor 

Invoke HCM Webservice to Load and Import the data. 

https://hostname/hcmCommonDataLoader/HCMDataLoader?WSDL

• Operation - importAndLoadData
• Pass the content ID in the request message. -- dDocName from UCM upload response. 
• <ns0:Field name="dDocName">UCMFA00422909</ns0:Field>
• Pass the additional Parameters.

Get the Status of HDL load and import job. 

• Operation - getDataSetStatus
• Pass the content id in the input parameter in the following format.
• "ContentId="$UCMContentID
• Exmple : ContentId=12345

To Submit Extract and Get the Output from UCM

• Submit Extract
• URL - https://hostname:443/hcmService/FlowActionsService?WSDL
• Operation 
• SubmitFlow  - To Submit the job
• Provide Extract Name 
• Extract Run ID
• getFlowTaskInstanceStatus  - To Get the  Status
• Provide Extract Name
• Provide Extract Run Id
• getIntegrationContentId  - To Get the content ID
• Provide Extract Name
• Provide Extract Run Id

• Get the Content from UCM.
• URL - https://hostname:443/idcws/GenericSoapPort?WSDL
• Service IdcService="GET_FILE"
• dDocName = ContentId
• Decode the Data and Write the file. 

UCM Details:

• Web Service 
• URL - https://hostname:443/idcws/GenericSoapPort?WSDL

BIP Webservices:

• To get the list of Webservices 
• URL - http://hostname/xmlpserver/services
• To Schedule a BIP Job.
• https://hostname/xmlpserver/services/v2/ScheduleService?wsdl
• Operations
• scheduleReport - To Submit the Job
• getScheduledReportStatus - To get the Status of the Job.
• getAllJobInstanceIDs  - To Get the Job Id's
• getXMLData  - To Get the XML Data 

Sample Payload : 

HCM Extract : 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/" xmlns:flow="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowControllerService/">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:submitFlow>

         <typ:flowName>SAMPLE_EXTRACT_REPORT</typ:flowName>

         <!--Zero or more repetitions:-->

         <typ:parameterValues>

            <flow:ParameterName>Effective Date</flow:ParameterName>

            <flow:ParameterValue>2019-10-17</flow:ParameterValue>

         </typ:parameterValues>

         <typ:parameterValues>

            <flow:ParameterName>LookbackDate</flow:ParameterName>

            <flow:ParameterValue>2019-10-02</flow:ParameterValue>

         </typ:parameterValues>

         <typ:parameterValues>

            <flow:ParameterName>RunDate</flow:ParameterName>

            <flow:ParameterValue>2019-10-17</flow:ParameterValue>

         </typ:parameterValues>    <typ:flowInstanceName>SAMPLE_EXTRACT_REPORT191017084530</typ:flowInstanceName>

         <typ:legislativeDataGroupName>US Legislative Data Group</typ:legislativeDataGroupName>

         <typ:recurringFlag>false</typ:recurringFlag>

      </typ:submitFlow>

   </soapenv:Body>

</soapenv:Envelope>

HCM Get Extract Status :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:getFlowTaskInstanceStatus>         <typ:flowInstanceName>SAMPLE_EXTRACT_REPORT191017084530</typ:flowInstanceName>         <typ:flowTaskInstanceName>SAMPLE_EXTRACT_REPORT</typ:flowTaskInstanceName>

         <typ:legislativeDataGroupName>US Legislative Data Group</typ:legislativeDataGroupName>

      </typ:getFlowTaskInstanceStatus>

   </soapenv:Body>

</soapenv:Envelope>

HCM Get Content ID of an Extract :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:getIntegrationContentId>         <typ:flowInstanceName>SAMPLE_EXTRACT_REPORT191017084530</typ:flowInstanceName>         <typ:flowTaskInstanceName>SAMPLE_EXTRACT_REPORT</typ:flowTaskInstanceName>

         <typ:legislativeDataGroupName>US Legislative Data Group</typ:legislativeDataGroupName>         <typ:integrationName>IntegrationIdDefinedinExtractDeliveryType</typ:integrationName>

      </typ:getIntegrationContentId>

   </soapenv:Body>

</soapenv:Envelope>

Sample HDL File Load :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/hcm/common/dataLoader/core/dataLoaderIntegrationService/types/">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:importAndLoadData>

         <typ:ContentId>12345</typ:ContentId>         <typ:Parameters>ImportMaximumErrors=100,LoadMaximumErrors=100,LoadConcurrentThreads=4,FileEncryption=PGPSIGNED,DeleteSourceFile=N</typ:Parameters>

      </typ:importAndLoadData>

   </soapenv:Body>

</soapenv:Envelope>

UCM Get File

<GenericRequest xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" webKey="cs">
<tns:Service IdcService="GET_FILE">
<tns:User/>
<tns:Document>
<tns:Field name="dDocName">Doc_Id233088</tns:Field>
<tns:Field name="RevisionSelectionMethod">Latest</tns:Field>
<tns:Field name="Rendition">Web</tns:Field>
</tns:Document>
</tns:Service>
</GenericRequest>

UCM Search 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM">

   <soapenv:Header/>

   <soapenv:Body>

      <ucm:GenericRequest webKey="CS">

         <ucm:Service IdcService="GET_SEARCH_RESULTS">

            <ucm:Document>

               <ucm:Field name="QueryText">dID &lt;MATCHES&gt; `422909`</ucm:Field>

              </ucm:Document>

         </ucm:Service>

      </ucm:GenericRequest>

   </soapenv:Body>

</soapenv:Envelope>

Upload File To UCM:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM" xmlns:wsse="http://schemas.xmlsoap.org/ws/2003/06/secext">

   <soapenv:Header/>

   <soapenv:Body>

      <ucm:GenericRequest webKey="cs">

         <ucm:Service IdcService="CHECKIN_UNIVERSAL">

            <ucm:User/>

            <ucm:Document>

               <ucm:Field name="dDocTitle">Department2 Load File</ucm:Field>

               <ucm:Field name="dDocType">Document</ucm:Field>

              <!-- <ucm:Field name="dDocAuthor">test1.user</ucm:Field>

-->

               <ucm:Field name="dSecurityGroup">FAFusionImportExport</ucm:Field>

               <ucm:Field name="dDocAccount">hcm$/dataloader$/import$</ucm:Field>

               <ucm:Field name="primaryFile">sampleFile2.zip</ucm:Field>

               <ucm:File href="sampleFile2.zip" name="primaryFile">                  <ucm:Contents>UEsDBBQACAgIAIZyBVUAAAAAAAAAAAAAAAAQAAAAT3JnYW5pemF0</ucm:Contents>

               </ucm:File>

            </ucm:Document>

         </ucm:Service>

      </ucm:GenericRequest>

   </soapenv:Body>

</soapenv:Envelope>

Reference:

https://soavijaya.blogspot.com/2019/03/saas-urls.html?m=1