How to Create new custom Web ADI in Oracle Apps

Hello Friends, Today I am going to show you hoe to create new custom Web ADI in oracle apps. 1) Assign the "Desktop Integration" responsibility to your user.

Web ADI Error While Downloading Excel file :

WebADI Run-time error'1004' Method 'VBProject' of object '_Workbook' failed

 

 

Root Cause of the Error
MS Excel not enabled for trusted access to VBA Project Object Model
How to reproduce the error?
1. Navigate to General Ledger responsibility > Journals > Launch Journal Wizard
2. Select the Layout = 'Functional Actuals Single' and Content = 'None', and hit button 'Create Document'
3. WebADI Excel spreadsheet will be downloaded on your desktop
4. Open the file and error appears
Solution
For MS Excel 2010:
1. Go to File > Options > Trust Center
2. Bit the button 'Trust Center Settings'
3. Click the checkbox 'Trust access to VBA project object model' 
4. Now retry opening the WebADI spreadsheet and the error will not appear.

How to Create Dialogue Page or Pop up window in OA Framework

How to Create Dialogue Page or Pop up window in OA Framework

 

Today I will show you how can we create a dialogue page in OAF. For example we have a page with apply button, On Apply button, We want to show dialogue page which will display message as " Do you really want to save the record?" with Cancel or Proceed button on it. When user click on Proceed then data should get saved into the table and if clicks on cancel then it should not save the record.

 

XXAP_SAVE DATA is fnd_message created in oracle apps which will store values as "Do you really want to save the record?"

 

CO Code:

 

if ("Apply"!=null)

{

OAException descMesg =  new OAException("XXAP", "XXAP_SAVE DATA", null, OAException.WARNING, null);
OADialogPage dialogPage = new OADialogPage(OAException.WARNING, descMesg,null, "", "");

 

                        dialogPage.setOkButtonToPost(true);
                        dialogPage.setNoButtonToPost(true);
                        dialogPage.setPostToCallingPage(true);

                        dialogPage.setOkButtonItemName("ProceedYes");
                        dialogPage.setNoButtonItemName("CancelYes");
                        dialogPage.setOkButtonLabel("Proceed");
                        dialogPage.setNoButtonLabel("Cancel");
                        pageContext.redirectToDialogPage(dialogPage);

 

                 // if user clicks Apply button, then record will save
             if (pageContext.getParameter("ProceedYes") != null)
             {  
                    am.invokeMethod("SaveChanges");
             }        
                // if user clicks No button, then it will rollback the transaction.
             else if (pageContext.getParameter("CancelYes") != null)
             {
             am.invokeMethod("rollbackChanges");
             }

}

 

AM Code:

 

                    public void rollbackChanges()
                    {
                    getOADBTransaction().rollback();
                    }

 

                    public void SaveChanges()
                    {
                    getOADBTransaction().commit();
                    }

the table cannot be sorted because it has pending changes which would be lost

Hello friends,

If you ever face the above issue or similar issue like The search cannot be executed because the table has pending changes that would be lost.      

Open your VOROwimpl.java file. search for the transient attribute in the vo.

Eg. if your transient attribute is RforceAccountId then search for setRforceAccountId method and comment the setattribuetinternal method and put populateattribute method instead of that. see the screen shot below:

                  

After changing this deploy the latest rowimpl.java to server and compile it and bounce the apache.

Your issue will be resolved.
 

500 Internal Server Error

oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException: Exception creating new Poolable object.

If you are getting the above error while running the page from Jdeveloper that means you have opened so many sessions and not logged out them.

For this you will have to ask DBA to kill the sessions.

Once you start running page from JDeveloper, every time log out instead of directly closing the window.

How to find out nth highest salary of an employee

SELECT Salary, id
FROM
(
SELECT Salary,id,DENSE_RANK() OVER(ORDER BY Salary ASC) Row_numbr from emp1
) tbl
WHERE Row_numbr=n

In the above example if you want to find out the 3rd higest salary of an employee, then pass 3 in place of n.

PL SQL COMPOSITE DATA TYPE

DATA TYPE :

 1) Scalar Data Type :

a) Number

b) Date

c) Varchar2

d) blob

e) clob

2) Composite data Type :

1) PL/SQL Records :

SET SERVEROUTPUT ON;

DECLARE
    TYPE emprectype IS RECORD ( name     VARCHAR2(50),
    hire_date      date,
    dob      DATE ,
    emp_no   number);
    emprec emprectype;
BEGIN
  select first_name || last_name , hire_date,dob,emp_no
  into emprec
  from emp
  where emp_no=100;
    dbms_output.put_line('Employee Name ' || emprec.name || ' has hire dat as  ' || emprec.hire_date  || ' and date of birth is ' || emprec.dob);
END;

insert data into table using records

SET SERVEROUTPUT ON;
DECLARE
    dept_rec   dept%rowtype;
BEGIN
    dept_rec.deptno := 90;
    dept_rec.dname := 'FINANCE';
    INSERT INTO dept VALUES dept_rec;
    COMMIT;
END;

update data into table using records

SET SERVEROUTPUT ON;
DECLARE
    dept_rec   dept%rowtype;
BEGIN
    dept_rec.deptno := 90;
    dept_rec.dname := 'FIN-updated';
    dept_rec.loc := 'USA';
  update dept set row = dept_rec where  deptno=90;
    COMMIT;
END;

2) COLLECTIONS:

a) PL/SQL Table :

Already covered in previous posts

b) VArray :

example 1:

SET SERVEROUTPUT ON;

DECLARE
    TYPE varry_typ IS
        VARRAY ( 10 ) OF NUMBER;
    varr   varry_typ;
BEGIN
    varr := varry_typ ();
    varr.extend ();
    varr(1) := 85;
    varr.extend ();
    varr(2) := 88;
    dbms_output.put_line('varr 1 value is '
                           || varr(1)
                           || ' varr 2 value is '
                           || varr(2) );

END;

Example 2:

SET SERVEROUTPUT ON;

DECLARE
    TYPE varry_typ IS
        VARRAY ( 10 ) OF NUMBER;
    varr   varry_typ;
BEGIN
    varr := varry_typ (11,22,33,44,55,66,77);
    dbms_output.put_line('varr 1 value is '
                           || varr(1)
                           || ' varr 2 value is '
                           || varr(2) );

END;

Example 3:

varr.Exists(5) means it will written true if 5th element in the array exists. if not exist then will written value as false.

varr.first will return you first element sequence not value and varr.last will written the last available sequence  not value in varray.

varr.PRIOR(2) will give the value in the 1st element value.

varr.NEXT(2) will give the value of 3rd element

varr.EXTEND() will allocate the memory so that we can assign value tot he element further. without this we can't assign the value to any element of varray. If we want to assign the value to two elements then we have to write this twice.

varr.trim(1)  will delete the last available value from the varray.

varr.trim(2)  will delete the last two available values from the varray.

varr.count  will give you only those values which are not deleted yet. if its trimmed then it won't count those values further.

SET SERVEROUTPUT ON;
DECLARE
    TYPE varry_typ IS VARRAY ( 10 ) OF NUMBER;
    vemp   varry_typ := varry_typ(11,22,33,44,55,66,77);
BEGIN
dbms_output.put_line('vemp count ' || vemp.count);
dbms_output.put_line('vemp limit ' || vemp.limit);
dbms_output.put_line('vemp first ' || vemp.first);
dbms_output.put_line('vemp last ' || vemp.last);
vemp.trim(2);
dbms_output.put_line('after vemp count ' || vemp.count);
dbms_output.put_line('after vemp limit ' || vemp.limit);
dbms_output.put_line('after vemp first ' || vemp.first);
dbms_output.put_line('after vemp last ' || vemp.last);
dbms_output.put_line('vemp 1 ' || vemp(1));
END;

c) Nested tables:

only difference between nested table and varray is the size. in varray size is limited whereas in nested table size is not fixed at the time of design. Also nested tabke is not dense as we can delete any element even from the middle as well. There is no limit function in this as its unbounded.

SET SERVEROUTPUT ON;
DECLARE
--TYPE varry_typ is varray(10) of number;
    TYPE varry_typ IS table OF NUMBER;
    vemp   varry_typ ;
    cursor c1 is
    select emp_no from emp;
    counter number :=1;
BEGIN
vemp :=varry_typ();
for i in c1 loop
vemp.extend();
vemp(counter) := i.emp_no;
dbms_output.put_line( 'vemp(' || counter || ') ' || vemp(counter) );
counter := counter + 1;
end loop;
END;

d) index by tables: associative array;

it has both the feature of nested table and as well as varray too. Only good thing is that in this it s defined by key and value structure means key should always be unique and not null.There is no limit function in this as its unbounded.

SET SERVEROUTPUT ON;

DECLARE
    TYPE l_empnotab IS TABLE OF number INDEX BY PLS_INTEGER;
    l_emparr   l_empnotab;
BEGIN
l_emparr(1) := 56;
l_emparr(3) :=78;
END;

in the above example 1 and 3 are key and 78 and 56 are values.No need to allocate memory in this table.

l_emparr.exists(n) return the true if any key is matching with value on n.

l_emparr.first will return you the smallest key value.

l_emparr.prior(3) will return as 1 because 1 is the key which is below 3 value.

 l_emparr.delete(7,10) means delete the elements from 7 to 10 means delete 7,8,9,10.

Below example to use rowtype in pl/sql table type:

SET SERVEROUTPUT ON;

DECLARE
    TYPE l_empnotab IS
        TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
    l_emparr   l_empnotab;
    i          NUMBER :=1;
    y          emp%rowtype;
BEGIN
    SELECT * BULK COLLECT
    INTO l_emparr
    FROM emp;
    LOOP
        dbms_output.put_line('employee number:' || l_emparr(i).emp_no);
        i := l_emparr.next(i);
        EXIT WHEN i IS NULL;
    END LOOP;
END;