TIMESTAMP - Datatype

We all know the basic datatypes in oracle like CHAR, VARCHAR, NUMBER, DATE . But most of them will not have the exposure to TIMESTAMP datatype. I like to share my view on this datatype today.

First let us know the difference in DATE and TIMESTAMP datatypes. We generally say both used to store date information. But there is a huge difference in it.

DATE

Its a dataype in which we can store the information like DATE,MONTH,YEAR with HOUR,MINUTE,SECONDS information. We normally call DD-MON-YYYY as a date part and HH24:MI:SS as a timestamp .

TIMESTAMP

Its also a datatype in which we can store date part as well as timestamp. Additionally it also stores the fractional part of seconds and the timezone information.

we can get the timezone information from the below query

select * from v$timezone_names;

As like sysdate we also have systimestamp which gets you the following information.

select systimestamp from dual;

The following example queries may explain you about the timestamp data type.

select to_char(timestamp_col,'YYYY-MM-DD"T"HH:MM:SS.FF3"Z"') time_stamp from tab_name;

Output : 2012-03-20T02:03:28.000Z


In the above example we already know about the format type YYYY-MM-DD HH:MM:SS .
.FF3 - It limits the fraction part as 3 . we can also use .FXX to get all the fraction part in the timestamp

select to_timestamp('2012-03-20T02:03:28.000Z','YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') from dual;

The above query is the reverse query.

we have more functions regarding timezone conversion. For more information you can always google it.

Item Catalog Group

Item - Its the base element in Oracle Inventory. During Implementation the items will be loaded through "Item Import" . Its a standard program to load items from Interface table. Its always a best practice to use "Item Template" to load items. In this way we can create similar type of items.

One other thing you can think about at that point is Item Catalog Groups. With Item Catalog Groups in Oracle you can catalog your items in groups and add extra information to your items based upon the category the items is related to.

Creating catalog groups and placing items in catalog groups will have several benefits. One of them obviously is that you will be able to group items and search within groups. Other benefits are that you will be able, for example, add more information which is relevant only for the items within this group and you will be able to automatically create aƒ item description based upon this. So this will also help you bring uniformity in the way you create your item descriptions.

To be able to do so you first will have to create a Catalog Item group within Oracle Inventory. To do so navigate to "Setup -> Items -> Catalog Groups". Here you will be able to view, edit and create new catalog groups.





As shown in the above screenshot I created a new Catalog Group in Oracle Inventory named "Gear Pumps" which will hold all the items which are gear pumps. Or in other words, in alter stage I will assign the Catalog Group "Gear Pumps" to all the existing items who are gear pumps. If you create a new group, save it and click on the Details button you will be able to set more options.



As you can see in the above screenshot I have set some details for this Item Catalog Group. I have been adding Descriptive Elements. Descriptive Elements are used per Item. In this case I have chosen 4 Descriptive Elements which can be set for all the items which will be placed this Item Catalog Group. The sequence is deliberately defined as 10, 20, 30, 40 because in this case I can easily add a record in the middle without having to change all the sequences of the existing records. This is in general a good idea to do. I also have set for all 4 of the Descriptive Elements that they are required so when a item is added to the group you have to set it. Only for 3 of the 4 I have set that it is a Description Default. When you set a Descriptive Element to Description Default it will be by default used to generate you description.

When you like to add a item to a Item Catalog group you go to the Item Master and query the item, then go to the Tools menu and select Catalog. you will be presented with a screen similar as in the screenshot below.



In this case I have selected the Item Catalog Group I created " gear Pumps" in the top "Catalog Group" field. When you select it you will be presented with a list of all the available and active Item Catalog Groups. After filling all the requierd fields for the Descriptive Elements, in this case all, you can use the Update Description button which will fill the item description with the generated "Item Catalog Description" based upon the catalog name and the descriptive elements in combination with their sequence. This is how you can generate standardized item descriptions. You do however have the possibility to change and edit the description so you will ne bound to the generated description. If you set this up smartly however it can be very beneficial.

AND - Binary Function - Left to Right execution

Hi , We all know about the AND function in Oracle PL/SQL . But only least know about the importance of the execution method. Let us start with the functionality of a AND function.
AND is a binary function which has two input and one output. The input and output will be either a Boolean TRUE / FALSE.

AND				F				T
F				F				F
T				F				T

Above is the Truth table which explains the AND function. So if one of the input is FALSE the AND function will return FALSE no matter about the second input . This is the thing we normally remember about the AND function.

But we also have to remember the execution sequence of it. For example we can take the below cases. We have a AND between two functions which returns a Boolean value.

fun1 - will always insert a data into a table and returns TRUE
fun2 - does nothing and returns FALSE

Case 1 : fun1 AND fun2
Case 2: fun2 AND fun1

In both cases the result will be FALSE. But we have some difference in the execution.

Case 1 : fun1 AND fun2
In this case fun1 will be executed first , a data will be inserted into the table and TRUE will be returned. since the first input is TRUE, the AND will check the next input . In this case it checks the fun2 which returns FALSE. So the result will be FALSE.

Case2: fun2 AND fun1
In this case fun2 will be executed first , it returns FALSE. Since the first input is FALSE, the AND will not check (execute) the next input. Because as per AND logic if one input is FALSE it will always return FALSE , no matter about the second input.

Even thought both cases return FALSE , a data got inserted in Case 1 where as it doesn't happen in Case 2.

The main motive of the above writeup is to explain the execution sequence of a binary function in PL/SQL.

Oracle PL/SQL functions always follows a LEFT to RIGHT execution.

User Responsibility Issue

Sometimes we might face some issue in User Responsibilities. I want to share the issue which I faced.

A responsibility was assigned to my user and it got end dated in production. So if a clone was made from production the responsibility which was end dated comes as it is with the end date.

I need to use the responsibility in the cloned instance. So I removed the end date for the responsibility and saved it. But it was still unavailable for me to use. Then I started to trace the tables which causing the issue.

FND_USER – The first table to get my user_id for my user.

FND_USER_RESP_GROUPS_DIRECT – Is the table which has the list of responsibility shown in the form (which includes my responsibility which is not available to use)

FND_USER_RESP_GROUPS – which contains the responsibilities for the user available to use it ( this will not have my responsibility which has the issue)

WF_USER_ROLES – This is a part of the above view FND_USER_RESP_GROUPS which tell the active user roles.

WF_LOCAL_USER_ROLES – This is a part of the above view WF_USER_ROLES which has all the roles including the inactive one.

This is where the issue came. WF_LOCAL_USER_ROLES has my responsibility role but it has been end dated (effective_end_date). By updating this to 1/1/9999 (unlimited end date) I was able to rectify my issue.

Hope this is useful.