OIC - Import to ERP Technique 1 - Import Payables Invoices using import bulk data into Oracle ERP Cloud option | Enbale default callback | Subscribe callback

Usecase: Here, we will create a reusable child integration which will take the importable zip file(header file, line file, properties file) as base64 encoded and import to ERP and enable callback and we will also create a callback integration to subscribe the callback event upon ERP job completion. This is resuable integration so not only AP Invoice, we can also use for other import jobs also like GL, Fixed asset etc using a simple swich block.

High level Steps:

• Create FBDI Invoice header and line csv files
• Create .properties file
• Create zip file
• Upload to ERP using bulk import option
• Create a callback integration in order to get callback upon ERP job completion

Create FBDI file:

• Build a Scheduled integration
• List all the files from SFTP
• Iterate over the files and Download file on staging
• Read the file from staging in chunks and create the FBDI file.

Create .properties file:

APInvoices.properties

Syntax:

<job package name>,<job definition name>,<zip file prefix>,<Param1>,.....<ParamN>

/oracle/apps/ess/financials/payables/invoices/transactions/,APXIIMPT,APInvoices,#NULL,$BusinesUnitId,N,#NULL,#NULL,#NULL,1000, Source,#NULL,N,N,LedgerId,#NULL,1

Create zip file:

create zip file with following 3 files

APInvoices.zip

    APInvoicesInterface.csv

    APInvoiceLinesInterface.csv

    APInvoices.properties

Upload to ERP using bulk Import:

Configure oracle ERP cloud endpoint >> import bulk data into Oracle ERP Cloud >> Import Payables Invoices>>Enable callback>>Done

Mapping:

Zip file reference >> reference

zip file properties file name >> file name

Create a callback integration in order to get callback upon ERP job completion:

Configure oracle ERP cloud endpoint >>select option - receive callback message upon completion of FBDI bulk Import job submitted via another integration >>Import payables Invoices

Detailed screenshots:

Step1: create a reusable integration to import to ERP and enable callback

Flow diagram:

Configure rest trigger :

Put a condition branch for each type of job import.

Configure ERP cloud adapter to import and enable callback

Map the zip file as reference and file name

Map the scope level faults to rest trigger response.

Subscribe to callback events:

Testing:

What we can do after subscribing call back:

• Success Scenario:
• First we check if summary status succeeded
• Then assign the import payables request it to a variable
• Download ess jobs execution logs or execution report for the import payables req id.
• Write the zip file to stage and unzip.
• Share the reports or logs over a Notification mail
• Failure Scenario
• When the summary status not succeeded, just send a notification mail with the callback file reference as attachment. The attachment will include all the processes log files, zipped file omported, properties file, and any error files. Then the concerned team or support team can take care of the issue.

Detailed Screenshots:

Note: 

1. Sometimes we observe that import to ERP main table does not happen with different kind of failures but still we are getting summary status as success in callback, in such scenario, we suggest to implement a custom callback. 
2. If we use import payables invoice request id to download pdf report file, it does not, it simply provides a general import log file. To get the overall invoice import report, we can schedule the report ESS job and get the report. Follow my blog to downlaod the teport - https://soalicious.blogspot.com/2023/12/oic-erp-how-to-get-import-payables.html
3. Like for GL, import journal child process we are easily can download out file which includes all the status report.

12c SOA - Adapter warning Messages after Migrating a SOA Project from 11g to 12c in JDeveloper

 On : 12.1.3.x, 12.2.1.x version

When attempting to open a SOA project developed in JDeveloper 11.1.1.7.x (or lower version) in JDeveloper 12.1.3.x or 12.2.1.x, the following warning will be seen during project migration:

ERROR

-----------------------

Incorrect namespace definition for xxx.jca, xxx.bpel...

STEPS

-----------------------

1.- Open the Application in 11g JDeveloper

2.- Deploy to SAR for each SOA Project-> this creates sca__revX.0.jar in the deploy directory.

3.- Start JDeveloper in 12c then navigate to File -> Import... -> select "SOA Archive Into SOA Project" -> OK -> Next -> Composite Archive Browse... and select the sca__revX.0.jar from (2) -> Finish

4.- The composite.xml in the Design view will show, but the adapters will show red x's in the upper right corner hovering over one of the red x's will show an error like:

ERRORS:

- Adapter binding.jca missing sca.config entry

- Failed to instantiate SCAEndpoint:

xxxxxx

- Failed to instantiate SCAEndpoint:

xxxxxx

Cause

The composite.xml in the Design view will show, but the adapters will show red x's in the upper right corner hovering over one of the red x's will show an error like:

ERRORS:

- Adapter binding.jca missing sca.config entry

- Failed to instantiate SCAEndpoint:

xxxxxx

- Failed to instantiate SCAEndpoint:

xxxxxx

 

Solution

There is a workaround for this issue.

To fix the problem, edit the 11g Adapter JCA file within the composite jar.

Make the following changes as appropriate:

From:

adapter="JMS Adapter"

To:

adapter="jms"

From:

adapter="Database Adapter"

To:

adapter="db"

From:

adapter="MQ Series Adapter"

To:

adapter="mq"

From:

adapter="AQ Adapter"

To:

adapter="aq"

After these changes are made, save the .jca file in the composite jar, and re-try the import into JDeveloper 12c.

The composite.xml should no longer show the red 'x' errors for the affected adapters.

Compiling and Deployment to a SAR file will also be successful.

OIC to ERP Import Technique 2 - importBulkData using ErpIntegrationService and Oracle Erp Cloud Adpapter

Usecase: Here, we will create a reusable integration which will import the base64 encoded zip file to Oracle ERP using erpIntegrationService and importBulkData operation.

High level steps:

1. Create an OIC_Encrypt_Import appdriven Orchestration integration
2. Create assign required Variables
3. Encrypt file
4. Update job options for callback
5. Bulk Data Import

Detailed steps:

Step1: Create an OIC_Encrypt_Import appdriven Orchestration and configure rest request and response.

Relative resource URI: /

Verb: POST

Request Json:

{

"base64FileReference": "ref",

"filename": "name",

"documentTitle": "title",

"documentAccount": "account",

"encryptFlag":"Y",

"encryptMethod": "PGPUNSIGNED",

"fusionDecryptKeyAlias": "key",

"jobName": "name",

"JobParameters":"Params",

"notifyServiceAccount":"Y",

"callBackFlag":"Y"

}

Response:

{

"essLoadRequestId":"",

"essLoadStatus":"",

"ExceptionCode":"",

"ExceptionReason":"",

"ExceptionDetails":""

}

Test Payload:

{

  "jobName": "/oracle/apps/ess/financials/payables/invoices/transactions,APXIIMPT",

  "fileName": "apinvoices_1282023.zip",

  "base64FileReference": "ref",

  "documentAccount": "fin$/payables$/import$",

  "encryptMethod": "PGPUNSIGNED",

  "callbackFlag": "Y",

"jobParameters":"#NULL,Business unit id,N,#NULL,#NULL,#NULL,1000,Source name,#NULL,N,N,ledger id,#NULL,1",

  "encryptFlag": "Y",

  "documentTitle": "AP Invoice Encrypted File",

  "notifyServiceAccount": "Y",

  "fusionDecryptKeyAlias": "Test_KEY"

}

Step2: Create assign required Variables:

varContent: decodeBase64ToReference(FileReference)

varEncryptOption: ""

varJobOptions: ""

Step3: Encrypt file:

If encryptFlag = Y then
Take a stage and encrypt:

Choose operation: encrypt file

>>Specify file reference $varContent

>>Specify the file name

>>specify the output directory /output

>>Specify the PGP key to encrypt Test_key.

Update Variables:

varContent: encodeReferenceToBase64(FileReference)

varEncryptOption: concat("FileEncryption=", encryptMethod,",FA_ALIAS=",fusionDecryptKeyAlias,",CUSTOMER_ALIAS=",fusionDecryptKeyAlias)

Otherwise:

varContent: encodeReferenceToBase64($varContent)

Step4: Update job options for callback:

If callBackFlag !=N

varJobOptions: concat("EnableEvent=Y,", $varEncryptOption)

Otherwise:

varJobOptions: $varEncryptOption

Step5: Bulk data import

Configure Oracle ERP Cloud Endpoint >>Select Query create , update or delete information>>Browse by services, Select ERPIntegrationService ,,importBulkData operation>>Done

Document>>

Content: $I_content(base64 encoded)

FileNname: From rest request

Content Type : zip

Document Title: from rest

DocumentSecurityGroup: 'FAFusionImportExport'

documentAccount: from rest

job details:

jobname: from rest

parameterlist: jobParameters

NotificationCode: 10 (if notifyServiceAccount =Y)

callBackURL: #NULL

jobOptions: I_jobOptions

Detailed Screenshots:

OIC - ERP Import Technique 3 - Multi steps

The following high level steps need to follow:

1. Create an OIC_Encrypt_Import appdriven Orchestration
2. Create assign required Variables
3. Encrypt file
4. Update job options for callback
5. Upload to UCM
6. append comment
7. submit ESS job
8. getESSJobStatus of that import request
9. Get the child process id if any of the import process
10. Get the ESS job status of the child process of the import process.

Create an OIC_Encrypt_Import appdriven Orchestration:

Use following Rest Service Payload:

Request:

Relative resource URI: /

Verb: POST

{

"base64FileReference": "ref",

"filename": "name",

"documentTitle": "title",

"documentAccount": "account",

"encryptFlag":"Y",

"encryptMethod": "PGPUNSIGNED",

"fusionDecryptKeyAlise": "key",

"jobName": "name",

"JobParameters":"Params",

"notifyServiceAccount":"Y",

"fusionAcountingHubFlag:"Y",

"callBackFlag:"Y"

}

Response:

{

"essLoadRequestId:"",

"essLoadStatus:"",

"essImportRequestID":"",

"essImportStatus":"",

"essChildRequestID:"",

"essChildRequestStatus":"",

"ExceptionCode:"",

"ExceptionReason:"",

"ExceptionDetails:""

}

Test Payload:

{

  "jobName": "/oracle/apps/ess/financials/subledgerAccounting/shared,XLATXNIMPORT",

  "fileName": "XlaTransactionUpload_FAH.zip",

  "base64FileReference": "ref",

  "documentAccount": "fin$/fusionAccountingHub$/import$",

  "encryptMethod": "PGPUNSIGNED",

  "fusionAccountingHubFlag": "Y",

  "callbackFlag": "Y",

  "jobParameters": "9500,XlaTransactionUpload_FAH.zip,#NULL,#NULL,Y,F,S,Y,Y,Y",

  "encryptFlag": "N",

  "documentTitle": "FAH <System Name> System Encrypted File",

  "notifyServiceAccount": "N",

  "fusionDecryptKeyAlias": "Test_KEY"

}

Create assign required Variables:

I_content: encodeBase64ToReference(FileReference)

x_val: jobParameters >>"9500,XlaTransactionUpload_FAH.zip,#NULL,#NULL,Y,F,S,Y,Y,Y",

p_argument1: substring($x_val,1.0,index-within-string($x_val,n','))>>

x_val: substring-after($x_val,concat($p_argument1,","))

p_argument2: substring($x_val,1.0,index-within-string($x_val,n','))

x_val: substring-after($x_val,concat($p_argument2,","))

p_argument3: substring($x_val,1.0,index-within-string($x_val,n','))

x_val: substring-after($x_val,concat($p_argument3,","))

p_argument4: substring($x_val,1.0,index-within-string($x_val,n','))

x_val: substring-after($x_val,concat($p_argument4,","))

p_argument5: substring($x_val,1.0,index-within-string($x_val,n','))

x_val: substring-after($x_val,concat($p_argument5,","))

p_argument6: substring($x_val,1.0,index-within-string($x_val,n','))

x_val: substring-after($x_val,concat($p_argument6,","))

p_argument7: substring($x_val,1.0,index-within-string($x_val,n','))

x_val: substring-after($x_val,concat($p_argument7,","))

p_argument8: substring($x_val,1.0,index-within-string($x_val,n','))

x_val: substring-after($x_val,concat($p_argument8,","))

p_argument9: substring($x_val,1.0,index-within-string($x_val,n','))

x_val: substring-after($x_val,concat($p_argument9,","))

p_argument10:$x_val

p_jobPackage: substring-before(jobName,",")

p_jobName: substring-after(jobName,",")

I_encryptOption: ""

I_jobOptions: ""

I_encryptOptionAppendComment: ""

I_jobOptionsApppendComment: ""

Encrypt file:

IF encryptFlag="Y"

Configure Stage file action>>Choose operation: encrypt file, >>Specify file reference $I_content>>Specify the file name>>specify the output directory /output>>Specify the PGP key to encrypt Test_key.

updateVariables:

I_content: encodeReferenceToBase64(FileReference)

I_encryptOption: concat("FileEncryption=", encryptMethod,",FA_ALIAS=",fusionDecryptKeyAlias, ",CUSTOMER_ALIAS=",fusionDecryptKeyAlias)

I_encryptOptionAppendComment: concat("FileEncryption=", encryptMethod,";FA_ALIAS=",fusionDecryptKeyAlias, ";CUSTOMER_ALIAS=",fusionDecryptKeyAlias)

Otherwise:

I_content= encodeReferenceToBase64(FileReference)

Update job options for callback:

If callBackFlag !=N

I_jobOptions: concat("EnableEvent=Y,", $I_encryptOption)

I_jobOptionsAppendComment: concat("EnableEvent=Y;", $I_encryptOptionAppendComment)

Otherwise:

I_jobOptions: $I_encryptOption

I_jobOptionsAppendComment: $I_encryptOptionAppendComment

Upload to UCM

Configure Oracle ERP Cloud Endpoint >>Select Query create , update or delete information>>Browse by services, Select ERPIntegrationService ,,uploadFileToUCM operation>>Done

Map to UCM:

Content:$I_content(encodeReferenceToBase64() one of the zip file)

FileName: File name of the zip file

Content Type : zip

Document Title: "FAH CAS Encrypted file"

DocumentSecurityGroup: 'FAFusionImportExport'

DocumentAccount: "fin$/fusionAccountingHub$/import$"

append comment(if encryptFlag="Y")

Configure Oracle ERP Cloud Endpoint >>Select Query create , update or delete information>>Browse by services, Select ERPIntegrationService ,,apendFileComment operation>>Done

Document id: result from upload file to UCM >>153017

Comments: I_jobOptionsAppendComment

submit ESS job

Configure Oracle ERP Cloud Endpoint >>Select Query create , update or delete information>>Browse by services, Select ERPIntegrationService ,,submitESSJobRequest operation>>Done

Job package name: p_jobPackage>>/oracle/apps/ess/financials/subledgerAccounting/shared

job definition name: p_JobName >>XLATXNIMPORT

paramList:

p1>>result of UCM upload>>153017

p2>>file name >>from rest payload>>XlaTransactionUpload_FAH.zip

p3>>p_argument3>>#NULL

p4>>p_argument4>>#NULL

p5>>p_argument5>>Y

p6>>p_argument6>>F

p7>>p_argument7>>S

p8>>p_argument8>>Y

p9>>p_argument9>>Y

p10>>p_argument10>>Y

Now just send the import request id as response from this reusable integration.

Get ESS job status for the import:

Follow my below blog for the steps:

https://soalicious.blogspot.com/2021/07/oic-get-ess-job-status.html

How to get child process id:

https://soalicious.blogspot.com/2022/01/oic-erp-how-to-get-child-process-id.html

To get the Ess job status of the child process of the import process if any:

https://soalicious.blogspot.com/2022/08/oic-get-ess-job-status-of-child-process.html

OIC - Use of Translate function

Requirements: To remove the aposthrope (') from a string.

Format:

translate($variable_name,"'",'')

We can also replace one special char to another one using translate function.

S3 rest service call to get the file object using contents key name from postman

 

Step1: Provide verb and rest URL

Verb: Get

URL format: https://buketname.s3.regionname.amazonaws.com/keyname

Where keyname= file_object_path/File_object_name with extension

Step2: Choose type as AWS signature and provide the follow information:

1. access key

2. secret key

3. AWS region: us-west-2(for example)

4. Service Name: s3

Step4: Run it.

S3 rest service call to get key or object name using postman

Step1: Provide verb and rest URL

Verb: Get

URL format: https://buketname.s3.regionname.amazonaws.com/?QueryParameters

QueryParameters used:

list-type=2&prefix=filePath/&delimiter=/&query=Contents[?contains(key,' ')]

Step2: Choose type as aws signature and provide the follow information:

1. access key

2. secret key

3. AWS region: us-west-2(for example)

4. Service Name: s3

Step4: Run it.