OIC - Managing Sequence Numbers using lookup in OIC for Dynamic Target File Naming

Use Case:

We have a requirement to create a unique file name by appending the following components:

• A sequence number retrieved dynamically.
• A hardcoded value '00000000'.
• A Hong Kong time-converted timestamp in the format [Y0001][M01][D01][H01][m01][s01].

Solution:

Maintain Sequence Numbers Using a Lookup Table:

• Create a lookup table (e.g., SCM_INVENTORY_TRANSACTION_SEQ_Lookup) with an initial sequence number.
• Use the dvm:lookupValue function to retrieve the current sequence number dynamically during integration runtime.

Create a Reusable Integration to Update the Lookup:

• Develop a reusable integration to increment the sequence number in the lookup by 1 after each successful integration execution.
• This ensures the sequence number is unique and incremented only when the integration runs successfully.

Generate the Timestamp:

• Use the ns99:format-dateTime function with the Hong Kong timezone to convert the current datetime into the required format:[Y0001][M01][D01][H01][m01][s01].

Concatenate the Components:

• Combine the sequence number, hardcoded value '00000000', and the formatted timestamp to generate the unique file name.

Example file name format:

<FileNamePrefix_FirstPeriod>_<SequenceNumber>_<Timestamp>.dat

Solution codes:

Var_DatetimeStamp

ns99:format-dateTime(fn:adjust-dateTime-to-timezone(fn:current-dateTime(),ns0:dayTimeDuration("PT08H00M")), '[Y0001][M01][D01][H01][m01][s01]')

var_runno

fn:concat("00000000",dvm:lookupValue('SCM_INVENTORY_TRANSACTION_SEQ_Lookup', 'INT_ID', $var_interfaceID, 'SEQ', 'NA'))

Var_TargetFileName

concat(dvm:lookupValue('Common_SCM_Interface_Outbound_Lookup','Integration_Id', $var_interfaceID, 'FileNamePrefix_FirstPeriod', 'NA'),$var_runno,'_', $Var_DatetimeStamp,'.dat')

Lookup API and Request payload details:

API: /ic/api/integration/v1/lookups/{name}

verb: PUT

request:

{

"columns": [ "rightnow", "soap" ],

  "rows": [

    {

      "rowData": [   "RN1",   "SOAP1"  ]

    }, {

      "rowData": [    "RN2",   "SOAP2"    ]

    }  ],

  "name": "myLookup"

}

Absolute Endpoint URI:

concat("https://design.integration.ap-region-1.ocp.oraclecloud.com/ic/api/integration/v1/lookups/",<LookpuName>,"?integrationInstance=",<ServiceInstanceNameFromintegrationMetadata>)

Trigger request and response json payloads:

Request:

"ProcessRequest" : {

    "Metadata" : {

        "ParentProcessId" : "123456",

        "Interface_Id" : "INT_XXX_XX_XXXX",

        "ParentProcessStartDateTime" : "2019-09-06T12:41:48.589+00:00",

    },

    "ProcessDetails" : {

        "LookUpName" : "VAL1",

        "SourceColumnName" : "VAL2",

        "TargetColumnName" : "VAL3",

        "SourceColumnValue" : "VAL4",

        "TargetColumnValue" : "VAL5"

    },

    "GenericParameters" : [

        {

            "ParameterName" : "KEY1",

            "ParameterValue" : "VAL2"

        },

        {

            "ParameterName" : "KEY2",

            "ParameterValue" : "VAL2"

        },

        {

            "ParameterName" : "KEY2",

            "ParameterValue" : "VAL2"

        }

    ]

}

Response:

"ProcessResponse" : {

    "Status" : "YES",

    "ProcessDetails" : {

        "LookUpName" : "VAL1",

        "SourceColumn" : "VAL2",

        "TargetColumn" : "VAL3",

        "SourceValue" : "VAL4",

        "TargetValue" : "VAL5"

    }

}

Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/rest-api/op-ic-api-integration-v1-lookups-name-put.html

Screenshots for creating reusable lookup update integration:

Integration flow:

Configure trigger request - response.

Configure lookup update API

Map

Test: