OIC FTP - FTP adapter write to file - File name pattern not taking the pattern specified in the mapper

Case study: 

I want to create a dynamic file name to be written to FTP folder but the dynamic file name provided in the mapper is not used by OIC. 

The file name has been mapped in the ftp mapper but the file that is written to FTP uses the file name pattern specified in FTP adapter write file action.

Example: 

file name pattern under write file.action: demo.txt

File nane in the mapper under properies : newfilename

during run output filename came: demo.txt

But expected is : newfilename

Screenshots what we did:

FTP write adpater configure page:

File name mapped in the Mapper 

Solution steps:

We should update the file name and directory name under OutboundFTPHeaderType instead in the properties filename.

OIC HCM - How to schedule and download HCM extract and send to downstream applications using Oracle HCM Cloud Adapter

Usecase: Here , we will see how to schedule and download HCM Extract and send to downstream applications using Oracle HCM cloud adpater.

High level:

We will create 3 integrations and achieve our goal as follow:

1. One scheduler integration: This will only be used to schedule and call 2nd common extract integration and pass Effective Date name value pairs.
2. Common HCM extract integration: This oneway appdriven integration will do the following
1. FlowActionService >> SubmitFlow : will extract the data to ucm
2. getFlowTaskInstanceStaus : it will show extract status
3. If extract SUCCEEDED, get IntegrationContentId
4. Call main integration and pass the content id.
3. Main Integration:
1. GET_FILE from ucm using dDocName as content id and using GenericSoapPort wsdl service
2. Decode the file
3. Transformed the file to downstream specific format.
4. Call downstream app and send the file

Integration names used:

1. INT<Interfaceno>_<Source>To<Target>_interfacedetails_Sch_V1
2. Common_HCM_Extract_Trigger_V1
3. INT<Interfaceno>_<Source>To<Target>_interfacedetails_Main_v1

Detailed steps:

1st Schedule integration:

Step1: Schedule param:

• LastRunDate : ""
• Schedule_EffectiveDate : "YYYY-MM-DD"

Step2: Assign required variables:

• varInterfaceName: INT332
• var_ExtractDelayinSeconds: 60
• var_ParameterName : 'Effective Date'
• var_ParameterValue: ns94:format-dateTime(fn:curent-dateTime(),'[Y001]-[M01]-[D01]')

Step3: Switch and check

If $schedule_effectivedate = ns74:format-dateTime($schedule_effectivedate,'[Y001]-[M01]-[D01]') or 

$schedule_effectivedate = 'YYYY-MM-DD'

Step4: INVOKE OIC common extract integration.

and map below details:

• Interfacename
• Processname
• Correlationid as instanceid
• Extractdelayinseconds
• Lastrundate
• Extract params(effective date name and value)

Otherwise: Throw new fault:

Invalid date format input for effective date.

Step5: From Default fault handler, rethrow fault and from Global fault >> send the fault details to Data dog app or notification as per requirement.

Common HCM extract integration:

Step1: Rest Post trigger 

Payload:

{

"interfaceName":"",

"processName":"",

"correlationId":"",

"extractDelayInSeconds":"",

"sequenceNumber":"",

"lastRunDate":"",

"extractParameters":{

"parameter":[{"name":"","value":""}]},

"extractIntegrationName":[{"integrationName":""}]}

Step2: SubmitFlow:

Select created oracle hcm cloud adapter >> select Query. Creat, update or delete information >> select Service: FlowActionsService >> Operation: submitFlow

Map the following details:

1. Flowname
2. Pass the parameters (Effective Date and value)
3. FlowInstanceName: fn:concat($var_IntegrationName,fn:current-dateTime())
4. LagislativeDateGroup
5. RecurringFlag: false 

Step3: take a switch and check if submitFlow result = true

Step4: Take a while loop and chek the getFlowTaskInstanceStatus till extract completed true.

var_ExtractCompleted= 'false'

Select created oracle hcm cloud adapter >> select Query, Creat, update or delete information >> select Service: FlowActionsService >> Operation: getFlowTaskInstanceStatus

And map the following:

1. flowInstnaceName
2. flowTaskInstanceName : flowname
3. legislativeDataGroupName

Step5: take a switch and check getFlowTaskInstanceStatus >> result >> 'COMPLETED' Then 

Assign as below:

var_ExtractCompleted : true

Otherwise: nothing.

Step6: for each extract, repeating element: extractIntegrationName.  Check content id.

Select created oracle hcm cloud adapter >> select Query, Creat, update or delete information >> select Service: FlowActionsService >> operation: getIntegrationContentId

Pass the following:

1. flowainstanceName
2. flowTaskInstanceName : Flow name
3. legislativeDataGroupName
4. IntegrationName

Step7: take a stage and write the content id and process id

Step8: take a assign and store contentid response status.

Step9: take a switch and check if contentid status = 'SUCCEEDED'

THEN 

Take a logger and log always

Concat('Status of this content id:'ContentId,'is ', Status)

Otherwise throw new error.

Step10: read the contentid and process id using stage.

Step 11:  call the next main integration and pass below fields:

1. interfaceName
2. processName
3. correlationId
4. SequenceNumber
5. Ucmcontent details: 
1. content id
2. processs id
6. lastRundate
7. Connectivity properties:
1. Localintegration
1. Code : fetched from lookup
2. Version: fetched from lookup

Main Integration:

Step1: rest trigger

Payload:

{

"interfaceName":"",

"processName":"",

"correlationId":"",

"sequenceNumber":"",

"UCMContentDetails":[{"ContentId":"","ProcessId":""}],

"flowInstanceName":"",

"lastRunDate":""

}

Step2: Download file from ucm:

Step3: read the in segments and Write the file using stage and send to downstream apps

SQL working with dates

 How to use Dates, Timestamps and intervals in Oracle databases.

Data types available:

• Date
• Timestamp
• Timestamp
• Timestamp with time zone
• Timestamp with local time zonr
• Interval
• Year to month
• Day to second

Date:

The DATE datatype is used by Oracle to store all datetime information where a precision greater than 1 second is not needed. Oracle uses a 7 byte binary date format which allows Julian dates to be stored within the range of 01-Jan-4712 BC to 31-Dec-9999 AD.

Example (10-JUL-2004 17:21:30)

Date functions:

SYSDATE:

Returns the current date-time from the operating system of the database server.

SELECT SYSDATE 
FROM   dual;

SYSDATE
-------------------
10/07/2004 18:34:12

1 row selected.

CURRENT_DATE

ADD_MONTHS(date, months)

LAST_DAY(date)

MONTHS_BETWEEN(date, date)

NEXT_DAY(date, day)

NEW_TIME(date, timezone1, timezone2)

TO_CHAR(date, format):

Converts a specified date to a string using the specified format mask. If the format mask is omitted the NLS_DATE_FORMAT value is used.

SELECT TO_CHAR(SYSDATE, 'DD/MM/YY HH24:MI') 
FROM   dual;

TO_CHAR(SYSDAT
--------------
10/07/04 18:48

1 row selected.

For different parameters or format mask:

https://www.techonthenet.com/oracle/functions/to_char.php

TO_DATE(date_string, format):

Converts a specified string to a date using the specified format mask. If the format mask is omitted the NLS_DATE_FORMAT value is used.

SELECT TO_DATE('10/07/2004 13:31:45', 'DD/MM/YYYY HH24:MI:SS') 
FROM   dual;

TO_DATE('10/07/2004
-------------------
10/07/2004 13:31:45

1 row selected.

ROUND(date, format):

Returns a date rounded to the level specified by the format. The default value for the format is DD, returning the date without the fractional (time) component, making it represent midnight on the specified date, or the following date depending on the rounding.

SELECT SYSDATE, 
       ROUND(SYSDATE, 'HH24') 
FROM   dual;

SYSDATE             ROUND(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:54:24 10/07/2004 19:00:00

1 row selected.

TRUNC(date, format):

Returns a date truncated to the level specified by the format. The default value for the format is DD, truncating the fractional (time) component, making it represent midnight on the specified date. Using the TRUNC function allows comparison of dates without the time components distracting from the true meaning of the comparison. It is similar to the round function, except that it always rounds down.

SELECT SYSDATE, 
       TRUNC(SYSDATE, 'HH24') 
FROM   dual;

SYSDATE             TRUNC(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:55:44 10/07/2004 18:00:00

1 row selected.

TIMESTAMP

The TIMESTAMP datatype is an extension on the DATE datatype. In addition to the datetime elements of the DATE datatype, the TIMESTAMP datatype holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6. There are also two variants called TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. As their names imply, these timestamps also store time zone offset information.

Example (10-JUL-2004 17:21:30.662509 +01:00)

Select ...

From ...

Where datetime_col > to_date('16-Feb-2023', 'DD-Mon-YYYY')

Reference:

https://oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals

https://youtu.be/P0qVs5ukKUk?si=Kt6WUk3sz1-tIz00

SQL - Windows functions

SQL in a nutshell

 

SQL Wildcard Characters

A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the LIKE operator with WHERE clause to search for a specified pattern in a column.

Example:

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

The above example shows - get all the customers whose name starts with 'a'

Wildcard characters:

Symbol	Description
%	Represents zero or more characters
_	Represents a single character
[]	Represents any single character within the brackets *
^	Represents any character not in the brackets *
-	Represents any single character within the specified range *
{}	Represents any escaped character **

Using the % Wildcard

The % wildcard represents any number of characters, even zero characters.

Return all customers that ends with the pattern 'es':

SELECT * FROM Customers
WHERE CustomerName LIKE '%es';

Using the _ Wildcard

The _ wildcard represents a single character.

It can be any character or number, but each _ represents one, and only one, character.

Return all customers with a City starting with any character, followed by "ondon":

SELECT * FROM Customers
WHERE City LIKE '_ondon';

Using the [] Wildcard

The [] wildcard returns a result if any of the characters inside gets a match.

Return all customers starting with either "b", "s", or "p":

SELECT * FROM Customers
WHERE CustomerName LIKE '[bsp]%';

Using the - Wildcard

The - wildcard allows you to specify a range of characters inside the [] wildcard.

Return all customers starting with "a", "b", "c", "d", "e" or "f":

SELECT * FROM Customers
WHERE CustomerName LIKE '[a-f]%';

SQL NULL functions

NVL() function

NVL() function is used to provide a default value when the specified field is null. we can also use the COALESCE() function as alternative.

Syntax:

NVL(column_name, default_value)

For example

In the below table, unitOnOrder is optional and can be null.

P_Id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	Jarlsberg	10.45	16	15
2	Mascarpone	32.56	23
3	Gorgonzola	15.67	9	20

SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products;

In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL. To avoide such nullibility issue, we use NVL() as below:

SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0))
FROM Products;