OIC - Error occurred while processing activity decryptsf4. Error: java.io.IOException: Error in decrypting the stream for file f:

Usecase: We have received an encrypted file from source and while trying to decrypt it using stage action, throwing below error:

The error message reads:

"Error occurred while processing activity decryptsf4. Error: java.io.IOException: Error in decrypting the stream for file f: /ocid1_integrationinstance_oc1_ap_singapore_1_amaaaaaalbz6auaavjgs3l7ic3amccbdcvgvjg3zohgwapphstnp4u2rpukq/PzDTInsnEe_TfH17CejA/in/t332/download/SourceName_HCM_PayrollResults_20240827_123456.csv"

Root cause: 

The root cause of the error seems to be a mismatch between the key pairs used for encryption and decryption. 

Specifically, the key pair used to encrypt the file and the key pair present in the Oracle Integration Cloud (OIC) certificate store for decryption are different. This mismatch is preventing the successful decryption of the file, leading to the IOException error. 

Solution:

To resolve this issue, ensure that the same key pair is used for both encrypting and decrypting the file.

OIC - iCal expression for scheduling a task to run at 4 PM on June 30th and December 31st each year in the Oracle Integration Cloud (OIC) scheduler?

 Usecase: "Can you provide an iCal expression for scheduling a task to run at 4 PM on June 30th and December 31st each year in the Oracle Integration Cloud (OIC) scheduler?"

Solution:

FREQ=YEARLY;BYMONTH=6,12;BYMONTHDAY=30,31;BYHOUR=16;BYMINUTE=0

OIC - Need to format a dateTime object with a three character month

Requirement: We need to create a file name where we need to populate Month's name as MMM like Sep, Oct etc.

Solution:

fn:concat('HCM_FTE_',ns96:format-dateTime(fn:current-dateTime(),'[MNn,*-3]-[Y001]'),'.csv')

Note: for Caps month like SEP. OCT , use as [MN,*-3]

OIC HCM - Designing HDL Load for External Worker Data business object

Working...

Usecase: external worker data hdl import to HCM.

Requirements  & Considerarions:

1. From source , it has sftp and we need to download encrypted sensitive data file
2. Save a back of the encrypted file in oic embeded file server as once file download or read the file.will.be deleted from source.
3. Poll the file from oic sftp embededded server and decrypt the file.
4. Transform to hdl specific pipe delimited file
5. Load to hcm.
6. Archive the encrypted file.

Design approach:

1. We will create 2 integrations
2. Schedule integration:
1. It will schedule at a particular time, and 
2. Invoke source sftp connection and download the file to stage and 
3. invoke OIC SFTP CONNECTION and save it.
4. Call the main integration
3. Main integration:
1. It will download the file.to stage.
2. Decrypt the file using stage and pgp key.
3. Read in segments using stage and transform to hdl file.
4. Invoke hcm adpater connection and submit hdl
5. Take a while loop and check hdl.status using hcm adapter
6. Once, success, archive the file. 
7. For error, throw error to global fault and send a notification or send to data dog or log etc.

We can also check hdl errors in details using below steps:

https://soalicious.blogspot.com/2024/09/oic-hcm-how-to-check-or-export-report.html

Details screenshot:

TBD

OIC HCM - How to check or export the report after importing an HCM Business Object using HDL, and review the results for errors or validation issues.

Usecase: Here,we will see how to check or export the report after importing an HCM Business Object using HDL, and review the results for errors or validation issues. We will send UCM content id as input and get the error details.

Implementation steps:

1. Create a Data model and rtf template based BIP report to get the business object import/load error details.
2. Create an ESS job based on the BIP report
3. From OIC integration, call exportBulkdata  of erpIntegrationService using oracle hcm  cloud adapter and in the mapping provide the following details
1. Job name: Ess job package,job definition name ex: "/oracle/apps/ess/custom/oic/int111/out/,HDLErroLog_BIP_Custom_ESS"
2. Parameter list: ucm content id
3. Job options : "EnableEvent=Y"
4. Callbackurl: "#NULL"
4. Create another callback integration  and perform as below:
1. subscribe the event using Oracle Cloud ERP adapter
1. Select Receive business events raised within ERP cloud
2. Select ERP Integration Outboubd Event
3. Put a filter condition for the ess job name to get theb callback distingushly for the particular business object.
2. Get the report using hcm adapter CRUD:
1. Service- erpIntegrationService
2. Operation: getDocumentForDocumentId
3. Unzip the contents using stage action
4. List files using stage and with *.xml file pattern.
5. Transform the report data to standard format if needed.
6. Save the report to OIC sftp location or send a notification to desired DLs.

Data model SQL:

SELECT DISTINCT l.MESSAGE_SOURCE_LINE_ID,

                l.msg_text,

                fl.TEXT

FROM   fusion.hrc_dl_message_lines l,

       fusion.hrc_dl_data_set_bus_objs bo,

       fusion.hrc_dl_data_sets ds,

       fusion.hrc_dl_file_rows fr,

       fusion.hrc_dl_file_lines fl,

       fusion.hrc_dl_physical_lines pl

WHERE  ((l.message_source_table_name = 'HRC_DL_LOGICAL_LINES'

          AND l.message_source_line_id = pl.logical_line_id)

         OR (l.message_source_table_name = 'HRC_DL_PHYSICAL_LINES'

             AND l.message_source_line_id = pl.physical_line_id))

       AND l.data_set_bus_obj_id = bo.data_set_bus_obj_id

       AND ds.data_set_id = bo.data_set_id

       AND ds.ucm_content_id IN (:P_ucmContentID)

       AND fl.line_id = fr.line_id

       AND fl.data_set_bus_obj_id = bo.data_set_bus_obj_id

       AND fr.logical_line_id = pl.logical_line_id

       AND l.is_latest_flag = 'Y'

Filter Expr for ERP Integration Outbound event:

<xpathExpr

xmlns:ns0="http://xmlns.oracle.com/apps/financials/commonModules/shared">

$eventPayload/ns0:JOBS[ns0:JOBNAME='INT332_ADP_BIP_CUSTOM_ESS']

</xpathExpr>

Detailed steps with screenshots:

Step1: Create data model and BIP Report.

Step2: Create ESS job which calls the step1 BIP report.

Test ESS job from HCM:

Step3: once the hdl import completed, run exportBulkData operation of ErpIntegrationservice using Oracle cloud HCM adapter

Step4: create a callback integration and get report and transformed the reprot and save to OIC embedded sftp location for support help.

Event subscribe:

Get report

Unzip the contents

List unzipped files

Write file to sftp location.

OIC HCM - How to check hcm hdl import and load status and more details from hcm console.

Usecase: Here, we will see the steps how we can check the hdl import or load status from hcm side.

Steps:

Step1: my client group >> data exchange >> recent file loads >>search and you will see the recent load files. Capture the content id. 

Step2: import and load data >> show filters >> put content id and search >> click message