Unix script to extract File Name and Base Path from File Path

$ VAR=/home/me/mydir/file.c
$ DIR=$(dirname "${VAR}")
$ echo "${DIR}"
/home/me/mydir
$ basename "${VAR}"
file.c

PLSQL Table Type

Hi , Some time we may need to process a table of records for which you might need to create a temporary table to process the records.

To avoid SQL Table we can use PLSQL table as given below

DECLARE
   TYPE t_record IS TABLE OF VARCHAR (30);
   TYPE t_tab IS TABLE OF t_record;

   v_tab   t_tab := t_tab ( t_record ('1', '2', '3')
                          , t_record ('A', 'B', 'C')
                          );
BEGIN
   FOR i IN 1 .. v_tab.COUNT
   LOOP
      DBMS_OUTPUT.put_line (v_tab (i) (1) || v_tab (i) (2) || v_tab (i) (3));
   END LOOP;
END;
/

Specified pseudocolumn or operator not allowed here. in package PO_DOCUMENT_CHECKS_PVT

While approving the requisition the follow error may occur.

"Specified pseudocolumn or operator not allowed here. in package PO_DOCUMENT_CHECKS_PVT"

Issue was caused by conversion rate missing for the conversion between 2 currencies

Reference : Error ORA-00976 While Approving Approving Internal Purchase Requisition (Doc ID 2321227.1)

Simple script to convert a string into records

Script:

Output:

NUMBERS
--------------------
123
124
125
126

Script to Add Responsibilities

SET serveroutput ON
DECLARE
  ln_user_id NUMBER;
  ln_resp_id NUMBER;
  ln_app_id  NUMBER;
  CURSOR c1
  IS
    SELECT responsibility_id,
      application_id,
      responsibility_name
    FROM fnd_responsibility_tl
    WHERE responsibility_name   IN ( 'Application Developer', 'Functional Administrator','System Administrator')
    AND responsibility_name NOT IN
      ( SELECT DISTINCT rtl.responsibility_name
      FROM apps.fnd_compiled_menu_functions cmf,
        apps.fnd_form_functions ff,
        apps.fnd_form_functions_tl ffl,
        apps.fnd_responsibility r,
        apps.fnd_responsibility_tl rtl,
        apps.fnd_user_resp_groups urg,
        apps.fnd_user u
      WHERE cmf.function_id     = ff.function_id
      AND r.menu_id             = cmf.menu_id
      AND urg.responsibility_id = r.responsibility_id
      AND rtl.responsibility_id = r.responsibility_id
      AND cmf.grant_flag        = 'Y'
      AND r.application_id      = urg.responsibility_application_id
      AND u.user_id             = urg.user_id
      AND u.user_name           = 'KAARTHIB'
      AND ff.function_id        = ffl.function_id
      );
BEGIN
  SELECT user_id INTO ln_user_id FROM fnd_user WHERE user_name = 'KAARTHIB';
  FOR i IN c1
  LOOP
    fnd_user_resp_groups_api.insert_assignment (ln_user_id, i.responsibility_id, i.application_id, NULL, SYSDATE, NULL, NULL );
  END LOOP;
  COMMIT;
END;
/

Script to update Application User Password

SET SERVEROUTPUT ON
DECLARE
  v_user_name    VARCHAR2(30):=upper('USER');
  v_new_password VARCHAR2(30):='PASSWORD';
  v_status       BOOLEAN;
BEGIN
  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name, newpassword => v_new_password );
  IF v_status =true THEN
    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
    COMMIT;
  ELSE
    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
  END IF;
END;
/

FRM-10102: Cannot attach PL/SQL library

Hi All , Many of you might have faced the "FRM-10102: Cannot attach PL/SQL library" issue while opening a oracle form locally in your machine.To avoid the issue (Forms 10g) we should perform the following steps.

Assuming you stored all your FMB and PLL files in D:\resource folder (copied from your server).

Add the path D:\resource in the FORMS_PATH registry entry.
Or
Create a Environment variable named FORMS_PATH and add the value D:\resource

You might still face the issue if you didn't download the complete resource folder from your server.

Copying the complete resource folder from server is a time consuming process. Instead copy the required PLL which was shown in the error window. After closing the PLL missing error window, try to manually add any one PLL once you open the form.It will start asking the basic PLL files ( like APPCORE,JL,JA,FNDSQF,etc) . It will be about 30 to 40 files which you need to download and move to the resource folder. After that close the form and reopen it. Now it will open without any error.

Please update your comments if you still face any issues even after performing the above steps.

ORA-04091: table XXX is mutating, trigger/function may not see it

Let me explain how Mutating error happens with  an example

Let us take a business scenarios. Assume that there is a table with ID column , Transaction Date , Transaction Type and Transaction Value.

The business need is, if any record inserted into the table with Transaction Type as "Invoice" then it should automatically insert another record with Transaction Type as "Tax" with Transaction value as 10% of the Invoice Transaction Value.

Below is the script initially built for the requirement

DROP TABLE mytransactions;

CREATE TABLE mytransactions
  (
    id     NUMBER,
    tdate  DATE ,
    ttype  VARCHAR2(20) ,
    tvalue NUMBER
  );

DROP TRIGGER mytransactions_ai;

CREATE OR REPLACE TRIGGER mytransactions_ai AFTER
  INSERT ON mytransactions FOR EACH row
BEGIN
    IF (:new.ttype='Invoice') THEN
      INSERT INTO mytransactions VALUES
        (:new.id,sysdate,'Tax',:new.tvalue*0.1 );
      dbms_output.put_line('Tax Inserted');
    END IF;
END;
/

INSERT INTO mytransactions VALUES (1,sysdate,'Invoice',100);

When we try to execute the above insert script, we might end up with the below error.

ORA-04091: table MYTRANSACTIONS is mutating, trigger/function may not see it
ORA-06512: at "MYTRANSACTIONS_AI", line 3
ORA-04088: error during execution of trigger 'MYTRANSACTIONS_AI'

To overcome the mutating error, we should use AUTONOMOUS_TRANSACTION in the trigger.

CREATE OR REPLACE TRIGGER mytransactions_ai AFTER
  INSERT ON mytransactions FOR EACH row
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    IF (:new.ttype='Invoice') THEN
      INSERT INTO mytransactions VALUES
        (:new.id,sysdate,'Tax',:new.tvalue*0.1 );
      dbms_output.put_line('Tax Inserted');
      COMMIT;
    END IF;
END;
/

After modifying the trigger we should able to insert the records successfully.


        ID TDATE     TTYPE                    TVALUE
---------- --------- -------------------- ----------
         1 11-JAN-17 Invoice                     100
         1 11-JAN-17 Tax                          10

Incompatibility Setting for a Concurrent Program

- Incompatibility settings are used to prevent the program running parallel
- This can be set at Global or at Domain Level
- When we set it at Global, the program cannot be run parallel at any point of time. It is taken care by the Conflict Manager.
- To set the incompatibility at Domain level, we should understand about conflict domain.

Conflict Domain is nothing but a logic partition of the data available in the database. The data can be partitioned at many level. For example, SOB, OU or INV level.

For example, if the Program needs to be run parallel across the operating unit but sequential within the operating unit then we need to define the Conflict Domain

Steps to define Conflict Domain at Operating Unit level.
1. Create a concurrent program
2. Add parameters
3. Select one parameter as Conflict Domain in the setup (Available in the parameter window itself). The parameter should be defaulted from profile "MO: Operating Unit"
4. Set the incompatibility for the program for itself at Domain level

 By following the above setup, we can achieve the requirement.

GREATEST

Hi all , hope you aware of MIN and MAX function in Oracle SQL. These functions are used as grouping function which can calculate the minimum or maximum value in a particular column. Since we normally group against a column. 

Requirement : Let us assume we have a table shown in the picture . Students and their marks against each subject as separate column. Now we have to find the maximum mark scored by a student across the subjects ( that is maximum value between English, Maths and Science for a student) as shown in the picture.  

Solution:  

SELECT student_name, GREATEST (english, maths, science)   FROM student;

GREATEST is a SQL function used to get max value between columns.

PL/SQL Collection : Storing TABLE values into a TABLE TYPE

After a long time had a time to blog ...

Below are some of the PL/SQL Collections
OBJECT Type
TABLE Type
RECORD Type

Hope you can google about these to get more information.
In this article I like to explain how to retrieve records from a table and store it in a TABLE type.

Create a table emp_tb as given below and insert some values

CREATE TABLE emp_tb ( empid NUMBER , NAME VARCHAR2(20) );
INSERT INTO emp_tb VALUES (10, 'Bala');
INSERT INTO emp_tb VALUES (20, 'Kaarhtik');






Now create a OBJECT type same like the table structure.

CREATE OR REPLACE TYPE emp_type IS OBJECT (
   empid   NUMBER,
   NAME    VARCHAR2 (20)
);

Create TABLE type with the object object

CREATE OR REPLACE TYPE emp_tb_type IS TABLE OF emp_type;

Below is a PLSQL block to retrieve the data from emp_tb and store it in the emp_tb_type object.

SET serveroutput on
DECLARE
   v_tab   emp_tb_type;
   v_rec   emp_type;
BEGIN
   v_tab := emp_tb_type ();

   FOR i IN (SELECT * FROM emp_tb)
   LOOP
      v_tab.EXTEND ();
      v_rec := emp_type (i.empid, i.name);
      v_tab (v_tab.LAST) := v_rec;
   END LOOP;

   FOR i IN v_tab.FIRST .. v_tab.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_tab (i).empid || '-' || v_tab (i).name);
   END LOOP;
END;
/

v_tab - is a variable for the TABLE type emp_tb_type
v_rec - is a variable for the OBJECT type emp_type. This variable is create to assign the value in the TABLE type variable v_tab. This is how a record is inserted into the table type object

emp_tb_type() - This is a constructor function which will be created along with the OBJECT type definition. We need to call this to initialize the variable v_tab.

EXTEND() - This is used to create one record index (Last record in the TABLE type variable)

emp_type(i.empid,i.name) - This is how a record is created from a OBJECT type so that we can assign it to its variable v_rec

v_tab(v_tabl.LAST) - This is used to navigate to the end of the TABLE type record  so that we can store the new value at the end.

Later of the code is use to retrieve the records from the TABLE type to display.

Below is the sample output.
10-Bala
20-Kaarhtik

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.

WF DOCUMENT Manager Error

ORA - 04061 : existing state of has been invalidated

ORA - 04065: not xecute,altred or dropped package body

The above errors may occur in WF when we modify or execute any pLSQL WF Package.

Technically when you apply some PLSQL changes to the database that could impact the mailer/notification code, you should shutdown the agent listener service, apply the changes and re-start them. Compiling a PLSQL package in the Database invalidates the state of that package in other active sessions and the next time the other session accesses that package, it appears invalid though in real-time the package is valid.

To release the issue clear the cache and bounce the WF Listener, WF mailer and Agent Listener.

PO Re-Approval

A PO is AUTO Created from REQ and its in Approved status.

If we cancel any REQ line then it will propogate the changes to the PO and the PO will changed to 'Inprocess' States.

To Accept the changes , just login as the PO Buyer and query the PO Summary.

Then go to Tools -> Repsond To Changes.

A separate page will open and you can accept the changes which will cancel the corresponding PO line and it will move the status to 'Approved'.

PO Header Total Amount

The PO Header Total Amount is sum of line amount. It will not include the amount for cancelled lines. If its still picks from the cancelled lines check whether the lines are partially cancelled.

You can check this in the shipments.

For cancelled lines the CLOSED_CODE will be 'CLOSED' and the CANCEL_FLAG will be 'Y'.

If the line got fully cancelled then the QUANTITY will become zero.

,pl.cancel_flag,pl.unit_price,pl.quantity , ( pl.unit_price*pl.quantity) line_amount , sum(( pl.unit_price*pl.quantity)) over(partition by ph.po_header_id) total_amount

,pd.quantity_cancelled,pd.quantity_billed,sum(( pl.unit_price*pd.quantity_billed)) over(partition by ph.po_header_id) matched_amount

Getting More then 9 parameters in a batch file

While calling a batch file using parameters we can use the parameters as %1,%2,etc.. But the 10th parameters can not be called as %10. Instead we should shift the parameters using SHIFT command and move the parameter one left. By this we can use the parameter 10 as %9

You can test it by using the below batch file by passing 10 parameters

c:\>kaarthik.bat a b c d e f g h i j

REM kaarthik.bat - file to test parameters
echo off
echo Program to test parameters
echo Parameter 1 : %1
echo Parameter 2 : %2
echo Parameter 3 : %3
echo Parameter 4 : %4
echo Parameter 5 : %5
echo Parameter 6 : %6
echo Parameter 7 : %7
echo Parameter 8 : %8
echo Parameter 9 : %9
echo Parameter 10 : %10
SHIFT
echo Parameter 10 : %9

IS_NUMBER - Equivalent SQL Query

The below query is used to check whether a varchar column contains only NUMBER or NON NUMBER data.

SELECT DECODE (REPLACE (TRANSLATE ('1123', '1234567890', '##########'), '#'),
               NULL, 'NUMBER','NON NUMER') RESULT
  FROM DUAL;

RESULT
------
NUMBER
1 row selected.

---------------------------------------------------------------------------------

SELECT DECODE (REPLACE (TRANSLATE ('11a23z', '1234567890', '##########'), '#'),
               NULL, 'NUMBER','NON NUMER') RESULT
  FROM DUAL;

RESULT 
---------
NON NUMER
1 row selected.