OIC Gen3 - about actions

 How to Add actions:

1. Open the actions pane
2. Use the inline Menu

List of actions available:

OIC Gen 3 - Working with XSLT constructors

If we open toggle function pallet, by default we would not see the XSL constructors, we have to click the XSLT button to enable to see it.

If we drag the xsl statement and hover or drop it in front of the element, then it will be added as child component of the element.

If we drag the xsl statement and hover or drop it at the back of the element name, then it willbe added as parent component of the element.

Note:

• Select If statement to specify single condition
• use choose when otherwise to specify multiple conditions. 
• For looping logic, use for-each statment. 
• We can also use xslt output >> literal, text, attribute, copy-of and value- of  to set default values in the mapper. 
• Using a copy-of element, we can perform a deep copy of elements from source to target with a copy-of constructor if both have same set of elements.

Xslt code editor use cases:

• Create internal variables
• Correlate multiple data  sources grouped by key field using the xsl for each group constructor.
• Create target name value pairs.
• Implement push style xslt (call template, apply template)
• Write custom xslt functions
• Copy node sets

Edit xslt code in oracle jdeveloper:

• Export the code.
• Design the integration flow logic
• Open the empty map action
• Map one data value from each required structure to the target
• Validate and clodw the mapper
• Save and the close the integration
• Export the entire integration archive (.iar file), for projects export the .car file.
• Import the iar file oracle jdeveloper
• Create an OSB application and project
• Import the integration archive to the osb project.(service bus resources >> select zipped or archived files >> selct file >> import.
• Locate and open to edit the .xsl file
• Import the .xsl file(not the entire integration) back to the oic mapper.

OIC Gen 3 - Error handling | OIC Error Hospital | Global and scope level fault handlers | Fault and End actions

Why we need to implement error handling / designing beyond the happy path:

• What happens When don't implement fault handling logic of any kind. By default every error occurs runtime, deliver to the OIC error hospital which is part of the oic runtime environwmnt, hosting all your deployed integrations, this can include any faults like runtime or business faults, request timeouts, invalid payloads, internal error etc.
• When error hospital catches a fault,  the integration flow stops or terminated immediately
• If you work on the projects, it will show on the observe page.
• If the integration is sync app driven and fault happens, the error hospital sends the same fault details to the client.
• All the errors are visible in the visibility secrion >> error page.

Instead of allowing the error hospital to catch every fault, we can intentionally catch all fault using the global and scope fault handlers.

Best practice Examples of error handling:

1. Log the error but continue with the integration flow.
2. Invoke a secondary service for backup processing.
3. Log the error and then terminate the integration flow
4. Invoke another service for notification or error handling processing.
5. Reply to the integration glow's client with a custom error response.
6. Send an email notification to an external stakeholder or an internal administrator.
7. Invoke an OIC  process to initiate a process workflow involving manual intervention.

Implementing Global Fault handler:

1. By default, there is default global fault handler, where it has re-throw fault action which rethrows the fault to OIC error hospital.
1. All uncaught faults and errors go to global fault handler
2. Throw new fault from scope fault handler
3. Re throw fault from scope fault.handler.
2. Change the default handling logic apt for your integration like 
1. add logger, 
2. email notification.
3. Map custom data for a fault return for sync integrations
4. Mitigate the error contion with alt logic
5. Invoke an error handling service(another oic integration or external service)

Global fault handlers End actions;

1. Re throw fault : it will send the fault to error hospital, 
2. Throw new fault: it will send a custom fault details to error hospital
3. Stop:  Terminate the integration flow, no error hospital will be involved. Separate fault handling logic wii be there before the stop action.
4. Fault return:  explicitly sending the business faults to the client for sync service. No error hospital is involved.
5. Return : fault mitigation logic, invoking a backup service, then send the normal.data to the client. No error hospital is involved.

For each invoke , try to create a scope and define scope fault handler.

Faults occurring within handlers are caught up by the next higher handler.

Inner scope >> outer scope >> global >> error hospital

Scope or global fault objects will be created to fetch fault details in the failt handler.

Note: 

For my project, I have followed as below:

1. For each invoke>> take it in one scope and in the default fault handle >> throw new fault with code, reason and details.
2. In global fault handler >> we have send the the global failt details to data dog SaaS application for further support.
3. We have created a lookup like common_error_details_lookup where it captures the key, error type, error code, reason and details. Using the key like 'UCMUPLOADFAILED' we can fetch custom error details.

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)