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;

SQL Case Expression

The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Example:

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

The following SQL will order the customers by City. However, if City is NULL, then order by Country:

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

SQL Sequence

This is the primary key field that we would like to be created automatically with unique and sequential value every time a new record is inserted.

CREATE SEQUENCE syntax:

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. 

It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.

To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence)

INSERT INTO Persons (Personid,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen');

SQL Operators

 Using operators

Arithmetic operators: 

• + (Addition)
• - (subtraction)
• * (Multiplication)
• / (Division)
• % (Modulus) - it returns reminder.

Example:

SELECT 30 + 20;

Comparison Operators:

• = (equal to)
• != (Not equal to) or <>
• > (Greater than)
• < (Less than)
• >= (Greater than or equal to)
• <= (Less than or equal to)

Example:

SELECT * FROM Products

WHERE Price <> 18;

SELECT * FROM Products

WHERE Price >= 30;

Logical Operators:

• AND (To check for both conditions to be true)

SELECT * FROM Customers

WHERE City = "London" AND Country = "UK";

• OR (To check for one of the conditions to be true)

SELECT * FROM Customers

WHERE City = "London" OR Country = "UK";

• NOT (To negate the given condition)

SELECT * FROM Customers
WHERE NOT Country = 'Spain';

SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'A%';

• IN (To specify multiple possible values for a column

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

• BETWEEN (Between a certain range)

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

• ALL (TRUE if all of the subquery values meet the condition)

SELECT ProductName
FROM Products
WHERE ProductID = ALL
  (SELECT ProductID
  FROM OrderDetails
  WHERE Quantity = 10);

• LIKE (Search for a pattern)

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

• ANY (TRUE if any of the subquery values meet the condition)

SELECT * FROM Products

WHERE Price > ANY (SELECT Price FROM Products WHERE Price > 50);

• EXIST

SELECT SupplierName

FROM Suppliers

WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

• SOME

SELECT * FROM Products

WHERE Price > SOME (SELECT Price FROM Products WHERE Price > 20);

Bitwise Operators:

• & (Bitwise AND)
• | (Bitwise OR)
• ^ ( Bitwise exclusive or)