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;

BULK COLLECT AND BULK BINDING

Basic example of BULK COLLECT

SET SERVEROUTPUT ON; DECLARE
    TYPE l_emplnotab IS
        TABLE OF per_all_people_f.employee_number%TYPE;
    TYPE l_emplnametab IS
        TABLE OF per_all_people_f.full_name%TYPE;
    l_empnoarr     l_emplnotab;
    l_empnamearr   l_emplnametab;
    CURSOR c1 IS SELECT
                     employee_number,
                     full_name
                 FROM
                     per_all_people_f
                 WHERE
                     ROWNUM <= 10;
BEGIN OPEN c1; LOOP
    FETCH c1 BULK COLLECT INTO
        l_empnoarr,
        l_empnamearr;
    CLOSE c1;
    FOR i IN l_empnoarr.first..l_empnoarr.last LOOP
        dbms_output.put_line('employee number: '
                               || l_empnoarr(i)
                               || ' and employee name is :'
                               || l_empnamearr(i) );
    END LOOP;
    end;

Output of the above block:

 

employee number: 158204 and employee name is :Baumann, Albert J

employee number: 197145 and employee name is :Stanton, Dennis R.

employee number: 197942 and employee name is :Walker, Heseelyah Malina (Malina)

employee number: 198606 and employee name is :Adiga, Suresh

employee number:  and employee name is :Srinivasan, S.

employee number: 198615 and employee name is :Uppin, Vijay

employee number: 198613 and employee name is :Corcoran, Daniel

employee number: 160379 and employee name is :Stebbeds, Tracy D.

employee number: 188314 and employee name is :Marcial, Lisa Betty

employee number: 189610 and employee name is :Phillips, Dawn Nichole

 

BULK BIND EXAMPLE:

 

SET SERVEROUTPUT ON;
DECLARE
    TYPE l_empnotab IS
        TABLE OF test1.a%TYPE INDEX BY PLS_INTEGER;
    l_emparr   l_empnotab;
    l_size     NUMBER;
    k          NUMBER;
BEGIN
    l_size := 1000;
    FOR j IN 1..1000 LOOP
        l_emparr(j) := j;
    END LOOP;
--execute IMMEDIATE 'truncate table test1';
    FORALL i IN l_emparr.first..l_emparr.last
        INSERT INTO test1 VALUES ( l_emparr(i) );
    COMMIT;
END;

 

How to Create Ref Cursor and sys_refcursor in PL/SQL

Below basic example is to create a ref cursor to print employee name with email address for first 5 rows.

Ref cursor example

set serveroutput on;
declare
type emp_refcursortype is ref cursor;
emp_cursor emp_refcursortype;
emp_rec     per_all_people_f%rowtype;
query_stmt varchar2(300) := 'select * from per_all_people_f ';
v_gender varchar2(5):='F';
begin
if v_gender is null then
query_stmt := query_stmt || ' where rownum<=5';
open emp_cursor for query_stmt;
else
query_stmt := query_stmt || ' where sex = :v_gender and rownum<=5';
open emp_cursor for query_stmt using v_gender;
end if;
loop
fetch emp_cursor into emp_rec;
exit when emp_cursor%notfound;
dbms_output.put_line('Employee_name '|| emp_rec.full_name || ' with email address ' ||  emp_rec.email_address || ' with gender ' || emp_rec.sex);
end loop;
close emp_cursor;
end;

SYS_REFCURSOR DECLARATION

emp_cursor       SYS_Refcursor ; 

How to use iterator to process multiple rows of table in OA framework

Today I will show you how to use iterator for table rows to perform any validation.

For example you have to validate table rows for any duplicate item, and throw an error If any.

Below code will be written in AM
        

      public String DuplicateRecords(String eid,Object invItemId)
     {
        
         RowSetIterator iter = getXX_InclusiveSuppliesVO1().createRowSetIterator("iter");
         int i=1;
         String returnString = "N";
         while(iter.hasNext()) {
         Row currRow = iter.next();
getOADBTransaction().writeDiagnostics(this,"inside DuplicateRecords:"+i,1);
         i=i+1;
             String currEid = (String)currRow.getAttribute("Eid");
             Object itemIdObject = currRow.getAttribute("UniqueValue");
          //   String currInvItemId = (String)itemIdObject;
             String itemNumber = (String)currRow.getAttribute("Item");
             getOADBTransaction().writeDiagnostics(this," currEid:"+currEid+" itemIdObject:"+itemIdObject,1);
            if (!(("").equals(currEid) ) && !(("").equals(itemIdObject) ))
            {
                if(currEid.equals(eid) && invItemId.equals(itemIdObject))
                {
                    returnString = "The combination of same EID:"+eid+" and ITEM:"+itemNumber+" already available in Inclusive table";
                    break;
                }
               
             }
         }
         iter.closeRowSetIterator();
         return returnString;
     }

how to call pl/sql procedures with table type in oaf

relation between counters, reading and main contract table

Relation between usage covered line and counters and readings

select mtl_ln_itm.segment1,csi.serial_number from
okc_k_headers_b okh,
okc_k_lines_b ln,
okc_k_items ln_item,
mtl_system_items mtl_ln_itm,
okc_k_lines_b cln,
okc_k_items oki,
cS_COUNTERS cs,
CS_COUNTER_VALUES csv,
CS_COUNTER_GROUPS csg,
csi_item_instances csi
where okh.contract_number='XX-OKS-001'
and okh.id= ln.chr_id
and ln.lse_id=12
and cln.cle_id= ln.id
and oki.cle_id=cln.id
and cs.counter_id=to_number(oki.OBJECT1_ID1)
and csv.COUNTER_ID= cs.counter_id
and ln_item.CLE_ID = ln.id
and mtl_ln_itm.inventory_item_id= to_number(ln_item.OBJECT1_ID1)
and mtl_ln_itm.organization_id= to_number(ln_item.OBJECT1_ID2)
and mtl_ln_itm.segment1='&usage_item'
and csg.COUNTER_GROUP_ID=cs.COUNTER_GROUP_ID
and csi.instance_id =csg.source_object_id
and csi.serial_number ='&serail number'

How to call pl/sql package from OAF page which has Table type input and output parameters

Hello friends,

Today I am going to tell you how to call pl/sql package from OAF page which has Table type input and output parameters.

First we will see create record and table type and then PL/SQL Package.

1) create or replace TYPE "XX_TONER_REC_TYPE"  as OBJECT
                                                                 ( line_number    number
                                                                  ,inventory_item_id  number
                                                                   ,reason_code    varchar2(200)
                                                                   ,reason_value    number
                                                                   ,ordered_qty    number
                                                                   ,quote_line_id   number
                                                                  );
     create or replace TYPE "XX_TONER_TBL_TYPE" IS TABLE OF "XX_TONER_REC_TYPE";

create or replace TYPE "XX_TONER_REC_OUT_TYPE" AS OBJECT
                                                                      ( line_number    number
                                                                       ,validation_flag   varchar2(2)
                                                                       ,valid_err_msg    varchar2(1000)
                                                                       ,new_recomm_qty    number
                                                                       );
 create or replace TYPE "XX_TONER_TBL_OUT_TYPE" IS TABLE OF "XX_TONER_REC_OUT_TYPE";

2) create specification and body of the package

create or replace PACKAGE        "XX_IBE_EXDRECQTY_RSN_VLD_PKG"
AUTHID CURRENT_USER AS

    PROCEDURE xx_toner_reason_validation_p (p_source IN VARCHAR2,
                                                                           p_in_quote_header_id in NUMBER,
                                                                           p_Instance_id in NUMBER,
                                                                           p_toner_tbl_details IN XX_TONER_TBL_TYPE,
                                                                           p_toner_out_tbl OUT XX_TONER_TBL_OUT_TYPE
   ) ;

 END XX_IBE_EXDRECQTY_RSN_VLD_PKG;

create or replace PACKAGE BODY        "RAC_IBE_EXDRECQTY_RSN_VLD_PKG"
AS PROCEDURE xx_toner_reason_validation_p
     (p_source IN VARCHAR2,
   p_in_quote_header_id in NUMBER,
   p_Instance_id in NUMBER,
   p_toner_tbl_details IN xx_TONER_TBL_TYPE,
   p_toner_out_tbl OUT xx_TONER_TBL_OUT_TYPE
   ) IS

 ln_supply_rma_splcopy_control   VARCHAR2(1) := fnd_profile.VALUE ('XX_SUPPLIES_RMA_SPLCPY_CONTROL');
 ln_splcpy_days_back     NUMBER  := TO_NUMBER (fnd_profile.VALUE ('XX_SUP_LAST_ORDER_CHK_DAYS_SPLCPY'));
 ln_spilled_days_back     NUMBER  := TO_NUMBER (fnd_profile.VALUE ('XX_SUP_LAST_ORDER_CHK_DAYS_SPILLED'));
 lv_date_valid_date    DATE  := TO_DATE(fnd_profile.value('XX_IBE_OLD_HISTORY_VALID_DATE'));
 ln_rma_err_flag      VARCHAR2(2) := 'N';
 ln_splcopy_err_flag     VARCHAR2(2) := 'N';
 ln_spilled_err_flag     VARCHAR2(2) := 'N';
 ln_sold_to_org_id    NUMBER;
 ln_rma_qty      NUMBER;
 ln_chk_rma_is_not_used   VARCHAR2(2) := 'N';
 ln_new_recom_qty    NUMBER;
 ln_notes_err_flag    VARCHAR2(2) := 'N';
 ln_out_notes_err_msg   VARCHAR2(2000);
 ln_indx       NUMBER  := 0;
 ln_timeframe_cnt    NUMBER  := 0;
 lv_timeframe              VARCHAR2(40);
 ln_created_by             NUMBER;
 ln_chk_splcpy_old    VARCHAR2(2) := 'N';
 ln_ordered_qty_for_rma   NUMBER  := 0;
 ln_install_date     DATE ;
 lv_jtf_rma_reason    VARCHAR2(2) := 'N';
  BEGIN
   --Initializing the collection with an appropriate constructor
   p_toner_out_tbl := XX_TONER_TBL_OUT_TYPE();
  
   ln_out_notes_err_msg  := NULL;
   ln_notes_err_flag := 'N';
   ln_new_recom_qty := NULL;

   -- Functionality to switch ON/OFF this validation using flag from profile
   IF nvl(ln_supply_rma_splcopy_control,'Y') = 'Y' THEN

  --Get the cust_account_id
  BEGIN
   SELECT cust_account_id into ln_sold_to_org_id
   FROM aso_quote_headers_all
   WHERE quote_header_id = p_in_quote_header_id ;
    EXCEPTION
    WHEN OTHERS THEN
   ln_sold_to_org_id := NULL;
  END;

  --Get Install_date
  BEGIN
   SELECT INSTALL_DATE INTO ln_install_date
   FROM csi_item_instances
   WHERE INSTANCE_ID = p_Instance_id
    AND ROWNUM = 1;
    EXCEPTION
    WHEN OTHERS THEN
   ln_install_date := NULL;
  END;

  --Opening loop for the lines those has user entered reason notes
  FOR i in 1 .. p_toner_tbl_details.count LOOP

    -- RETURN reason validation start
    IF p_toner_tbl_details(i).reason_code = 'RETURN'
    AND p_toner_tbl_details(i).reason_value is not null THEN
   
   --RAM is valid or not
   BEGIN
    SELECT sum(oohl.ORDERED_QUANTITY) into ln_rma_qty
    FROM OE_ORDER_HEADERS_ALL ooha
     ,OE_ORDER_LINES_ALL oohl
     ,RCV_TRANSACTIONS rt
     ,OE_ORDER_LINES_ALL ool
    WHERE  1=1
    AND ooha.header_id        = oohl.header_id
    AND ooha.flow_status_code <> 'CANCELLED'
    AND ooha.header_id        = rt.OE_ORDER_HEADER_ID
    AND rt.OE_ORDER_LINE_ID   = oohl.line_id
    AND ooha.ORDER_NUMBER     =  p_toner_tbl_details(i).reason_value -- RMA number
    AND rt.SOURCE_DOCUMENT_CODE = 'RMA'
    AND rt.TRANSACTION_TYPE = 'RECEIVE'
    AND ooha.sold_to_org_id = ln_sold_to_org_id
    --AND oohl.attribute13    = p_Instance_id
    AND oohl.flow_status_code <> 'CANCELLED'
    AND oohl.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
    AND ooha.source_document_id = ool.header_id
    AND ool.line_id = nvl(oohl.SOURCE_DOCUMENT_LINE_ID,oohl.REFERENCE_LINE_ID)
    AND ool.inventory_item_id = oohl.inventory_item_id
    AND ool.attribute13    =  p_Instance_id ;
 
     EXCEPTION
     WHEN OTHERS THEN
    ln_rma_qty := null;
 
   END;
   -- The RMA has not been used already in other order for the same serial item
   BEGIN
    SELECT 'Y'
      INTO ln_chk_rma_is_not_used
    FROM DUAL
    WHERE NOT EXISTS
     (SELECT 1
      FROM aso_quote_headers_all aqh,
        aso_quote_lines_all aql,
        oe_order_headers_all ooh,
        oe_order_lines_all ool
     WHERE aqh.quote_header_id <> p_in_quote_header_id
       AND (aql.attribute6 like p_toner_tbl_details(i).reason_code||'%'||p_toner_tbl_details(i).reason_value||'%') -- RMA Number
       AND aqh.quote_header_id = aql.quote_header_id
       AND aql.attribute13 = p_Instance_id  -- Instance_id
       AND aql.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
       AND aqh.cust_account_id = ln_sold_to_org_id
       AND aql.quote_header_id  <> p_in_quote_header_id
       AND aql.LINE_QUOTE_PRICE = 0
       --
       AND ooh.ORDER_NUMBER     !=  p_toner_tbl_details(i).reason_value -- RMA number
       AND ooh.header_id    = ool.header_id
       AND ooh.source_document_id  = aqh.quote_header_id
       AND ooh.flow_status_code  <> 'CANCELLED'
       AND ool.flow_status_code  <> 'CANCELLED'
       AND ooh.sold_to_org_id   = ln_sold_to_org_id
       AND ool.attribute13        = aql.attribute13
       AND ool.inventory_item_id  = aql.inventory_item_id
       AND ROWNUM = 1
     );
    
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
    ln_chk_rma_is_not_used := 'N';
      WHEN OTHERS THEN
    ln_chk_rma_is_not_used := 'N';
   END;
 
   -- Check RMA used already for next 6 months and this execution is no longer
   lv_jtf_rma_reason := 'N';
   IF ln_chk_rma_is_not_used = 'Y' AND lv_date_valid_date > SYSDATE THEN
    BEGIN
     SELECT 'Y' into lv_jtf_rma_reason
      FROM jtf_notes_b jnb,
        jtf_notes_tl jtl,
        fnd_lookup_values flv,
        oe_order_headers_all ooh,
        oe_order_lines_all ool
      WHERE jnb.source_object_id <> p_in_quote_header_id
       AND ooh.ORDER_NUMBER     !=  p_toner_tbl_details(i).reason_value -- RMA number
       AND ooh.header_id    = ool.header_id
       AND ooh.flow_status_code  <> 'CANCELLED'
       AND ool.flow_status_code  <> 'CANCELLED'
       AND ooh.sold_to_org_id   = ln_sold_to_org_id
       AND ooh.CREATION_DATE >= SYSDATE - ln_splcpy_days_back
       AND ool.attribute13        = p_Instance_id
       AND ool.inventory_item_id  = p_toner_tbl_details(i).inventory_item_id
       AND jnb.source_object_id = ooh.source_document_id 
       AND jnb.jtf_note_id = jtl.jtf_note_id
       AND jnb.source_object_code = 'ASO_QUOTE'
       AND flv.LOOKUP_TYPE = 'XX_IBE_SHOPCART_EXCEED_REASON'
       AND flv.LOOKUP_CODE = 'RETURN'
       AND nvl(flv.END_DATE_ACTIVE,sysdate) >= sysdate
       AND (jtl.notes like '%'||flv.description||'%'||p_toner_tbl_details(i).reason_value||'%')
       AND ool.unit_selling_price = 0
       AND ROWNUM = 1;
      
       EXCEPTION
     WHEN NO_DATA_FOUND THEN
      lv_jtf_rma_reason := 'N';
     WHEN OTHERS THEN
      lv_jtf_rma_reason := 'N';    
    END;
   END IF;
 
   -- Added this logic to get total ordered qty already for entered RMA number
   ln_ordered_qty_for_rma := 0;
   IF ln_chk_rma_is_not_used = 'N' THEN
    BEGIN
     SELECT nvl(SUM (oeol.ordered_quantity),0)
      INTO ln_ordered_qty_for_rma
      FROM aso_quote_headers_all aqh1,
        aso_quote_lines_all aql1,
        oe_order_headers_all oeoh,
        oe_order_lines_all oeol
      WHERE aqh1.quote_header_id  <> p_in_quote_header_id
       AND (aql1.attribute6 like p_toner_tbl_details(i).reason_code||'%'||p_toner_tbl_details(i).reason_value||'%') -- RMA Number
       AND aqh1.quote_header_id  = aql1.quote_header_id
       AND aql1.attribute13   = p_Instance_id  -- Instance_id
       AND aql1.inventory_item_id  = p_toner_tbl_details(i).inventory_item_id
       AND aqh1.cust_account_id  = ln_sold_to_org_id
       AND aql1.quote_header_id   <> p_in_quote_header_id
       ---
       AND oeoh.ORDER_NUMBER     !=  p_toner_tbl_details(i).reason_value -- RMA number
       AND oeoh.source_document_id  = aqh1.quote_header_id
       AND oeoh.sold_to_org_id   = ln_sold_to_org_id
       AND oeoh.flow_status_code  <> 'CANCELLED'
       AND oeoh.header_id    = oeol.header_id
       AND oeol.flow_status_code  <> 'CANCELLED'
       AND oeol.attribute13        = p_Instance_id -- Instance_id
       AND oeol.inventory_item_id  = p_toner_tbl_details(i).inventory_item_id
       AND oeol.unit_selling_price  = 0;
     EXCEPTION
     WHEN OTHERS THEN
       ln_ordered_qty_for_rma := 0;
    END;
   END IF;
 
   ---------
    IF nvl(ln_rma_qty,0) = 0 THEN
    ln_notes_err_flag := 'Y';
    ln_out_notes_err_msg := 'RMA_INVALID';
    END IF;
    ---------
    IF (ln_chk_rma_is_not_used = 'N'
      AND nvl(ln_rma_qty,0) != 0
      AND nvl(ln_rma_qty,0) < p_toner_tbl_details(i).ordered_qty + ln_ordered_qty_for_rma) OR lv_jtf_rma_reason = 'Y' THEN
     ln_notes_err_flag := 'Y';
     ln_out_notes_err_msg := 'RMA_USED_ALREADY';
     ELSIF nvl(ln_rma_qty,0) != 0 AND nvl(ln_rma_qty,0) < p_toner_tbl_details(i).ordered_qty + ln_ordered_qty_for_rma THEN
    ln_notes_err_flag := 'Y';
    ln_out_notes_err_msg := 'RMA_EXCEEDED_QTY';
     END IF;
    -----------
    END IF;
    -- RETURN reason validation END
 
    -- When user selected Special copy/print project
    -- SPLCOPY reason validation Start
    IF p_toner_tbl_details(i).reason_code = 'SPLCOPY' AND p_toner_tbl_details(i).reason_value is not null THEN
   
   IF p_source = 'TOPS' THEN  -- Source IF start
    -- Calculating new recomm_qty based on user entered no of copies or AMCV
     BEGIN
      SELECT
     DECODE
      (isbi.item_type,
      'TONER', DECODE
       (isbi.chargeable_inclusive,
       'I', (ikn_sam_supplies_tool_pkg.get_allowed_qty_f
           (p_toner_tbl_details(i).reason_value,
         isbi.toner_yield,
         isbi.component_item_id,
         itii.external_reference,
         nvl(ln_install_date,itii.install_date),
         nvl(p_Instance_id,itii.instance_id)
           ))
       ,
       NULL
       ),
      NULL
      ) eid_allowed_qty INTO ln_new_recom_qty
      FROM ikn_telesales_ib_info itii,
       ikn_supplies_bom_info isbi
      WHERE isbi.instance_id = itii.instance_id
      AND itii.instance_id = p_Instance_id
      AND isbi.equipment_inv_item_id = itii.inventory_item_id
      AND isbi.component_item_id = p_toner_tbl_details(i).inventory_item_id
      AND isbi.item_type = 'TONER'
      AND parent_child_ind = 'S' ;
   
      EXCEPTION
       WHEN OTHERS THEN
      ln_new_recom_qty := NULL;
   
     END;
  
     ELSIF p_source = 'MYRICOH' THEN  -- Source else for MyRicoh
    
    --Check if timeframe exists in XX_ibe_pricing_cart table. If yes, then it is MS user's order
    BEGIN
     SELECT COUNT(1)
       INTO ln_timeframe_cnt
       FROM XX_ibe_pricing_cart
      WHERE quote_header_id = p_in_quote_header_id
        AND timeframe IS NOT NULL;
       EXCEPTION
      WHEN OTHERS THEN
      ln_timeframe_cnt := 0;
     END;
 
    IF ln_timeframe_cnt > 0 THEN
     --get the timeframe
     BEGIN
      SELECT timeframe,
          created_by
        INTO lv_timeframe,
          ln_created_by
      FROM XX_ibe_pricing_cart
      WHERE quote_header_id = p_in_quote_header_id
       AND quote_line_id   = p_toner_tbl_details(i).quote_line_id
       AND timeframe IS NOT NULL;
       EXCEPTION
      WHEN OTHERS THEN
       lv_timeframe := NULL;
       BEGIN
        SELECT CREATED_BY INTO ln_created_by
        FROM aso_quote_lines_all
        WHERE quote_header_id = p_in_quote_header_id
         AND quote_line_id = p_toner_tbl_details(i).quote_line_id ;
        EXCEPTION
          WHEN OTHERS THEN
         ln_created_by := NULL;
     
       END;
     END;
     ln_new_recom_qty :=
      XX_ibe_ms_utility_pkg.fetch_ms_rec_qty_f
          (lv_timeframe,
           p_toner_tbl_details(i).inventory_item_id,
           p_Instance_id,
           ln_created_by,
           p_toner_tbl_details(i).reason_value);
       ELSE  -- For Non MS User
     BEGIN
     SELECT
      DECODE
      (isbi.item_type,
      'TONER', DECODE
       (isbi.chargeable_inclusive,
       'I', (XX_ibe_supply_workflow_pkg.get_allowed_qty_for_splcopy_f
           (p_toner_tbl_details(i).reason_value,  --AMCV
         isbi.toner_yield,
         isbi.component_item_id,
         --itii.external_reference,
         nvl(p_Instance_id,itii.instance_id),
         nvl(ln_install_date,itii.install_date)
           ))
       ,
       NULL
       ),
      NULL
      ) eid_allowed_qty INTO ln_new_recom_qty
      FROM ikn_telesales_ib_info itii,
       ikn_supplies_bom_info isbi
      WHERE isbi.instance_id = itii.instance_id
      AND itii.instance_id = p_Instance_id
      AND isbi.equipment_inv_item_id = itii.inventory_item_id
      AND isbi.component_item_id = p_toner_tbl_details(i).inventory_item_id
      AND isbi.item_type = 'TONER'
      AND parent_child_ind = 'S' ;
   
      EXCEPTION
       WHEN OTHERS THEN
      ln_new_recom_qty := NULL;
   
     END; 
        END IF;
    
     END IF; -- Source IF end
   
    --There is not another Special project request order of the same item in the last 6 months
    BEGIN  
    SELECT 'Y'
      INTO ln_chk_splcpy_old
    FROM DUAL
    WHERE EXISTS
     (SELECT 1
      FROM aso_quote_headers_all aqh,
        aso_quote_lines_all aql,
        oe_order_headers_all ooh,
        oe_order_lines_all ool
     WHERE aqh.quote_header_id <> p_in_quote_header_id
       AND (aql.attribute6 like p_toner_tbl_details(i).reason_code||'%') -- Special Project
       AND aqh.quote_header_id = aql.quote_header_id
       AND aql.attribute13 = p_Instance_id  -- Instance_id
       AND aql.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
       AND aql.quote_header_id  <> p_in_quote_header_id
       AND aqh.cust_account_id = ln_sold_to_org_id
       AND aql.LINE_QUOTE_PRICE = 0
       --
       AND ooh.CREATION_DATE >= SYSDATE - ln_splcpy_days_back -- 6 months
       AND ooh.header_id    = ool.header_id
       AND ooh.source_document_id  = aqh.quote_header_id
       AND ooh.flow_status_code  <> 'CANCELLED'
       AND ool.flow_status_code  <> 'CANCELLED'
       AND ooh.sold_to_org_id   = ln_sold_to_org_id
       AND ool.attribute13        = aql.attribute13
       AND ool.inventory_item_id  = aql.inventory_item_id
       AND ROWNUM = 1
     );
    
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
    ln_chk_splcpy_old := 'N';
      WHEN OTHERS THEN
    ln_chk_splcpy_old := 'N';   
    END;
   ---------------
   IF p_toner_tbl_details(i).ordered_qty > nvl(ln_new_recom_qty,0)+1 THEN
    ln_notes_err_flag := 'Y';
    ln_out_notes_err_msg := 'SPLCPY_EXCEEDED_QTY';
   END IF;
   ----------------
   IF ln_chk_splcpy_old = 'Y' THEN
    ln_notes_err_flag := 'Y';
    ln_out_notes_err_msg := 'SPLCPY_ORD_EXISTS_IN_180_DAYS';
   END IF;  
    END IF;
    -- SPLCOPY reason validation End    
   
    ln_indx := ln_indx+1;
    p_toner_out_tbl.EXTEND;  --For next record
   
    IF ln_notes_err_flag = 'Y' THEN 
   /*p_toner_out_tbl(ln_indx).line_number := p_toner_tbl_details(i).line_number;
   p_toner_out_tbl(ln_indx).validation_flag := 'Y';
   p_toner_out_tbl(ln_indx).valid_err_msg := TRIM('|' from ln_out_notes_err_msg);
   p_toner_out_tbl(ln_indx).new_recomm_qty := ln_new_recom_qty ;*/
 
   p_toner_out_tbl(ln_indx):=XX_TONER_REC_OUT_TYPE (
            p_toner_tbl_details(i).line_number
              ,'Y'
              ,TRIM('|' from ln_out_notes_err_msg)
              ,NVL(ln_new_recom_qty,0) );
 
    ELSE
   /*p_toner_out_tbl(ln_indx).line_number := p_toner_tbl_details(i).line_number;
   p_toner_out_tbl(ln_indx).validation_flag := 'N';
   p_toner_out_tbl(ln_indx).valid_err_msg := NULL;
   p_toner_out_tbl(ln_indx).new_recomm_qty := NULL ; */
 
   p_toner_out_tbl(ln_indx):=XX_TONER_REC_OUT_TYPE (
            p_toner_tbl_details(i).line_number
              ,'N'
              ,NULL
              ,NVL(ln_new_recom_qty,0) );
   
    END IF;
   
    ln_notes_err_flag := 'N';
    ln_out_notes_err_msg :=  '';
    ln_new_recom_qty := NULL;
    lv_jtf_rma_reason := 'N';
   
    END LOOP;   -- Loop End
  
  END IF;
 
   EXCEPTION
   WHEN OTHERS THEN
   ln_notes_err_flag := 'N';
   ln_out_notes_err_msg :=  '';
   ln_new_recom_qty := NULL;
  END XX_toner_reason_validation_p;
END XX_IBE_EXDRECQTY_RSN_VLD_PKG;



3) Now in OAF first we have to call this procedure on the event of the field and show the output parameter value in the page .

below code should be written in the controller:

         if ("validateRmaSplEvent".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM)))
{
   String quoteHdrId = pageContext.getParameter("Quote_Header_Id");
   String instanceId = pageContext.getParameter("EID_Model");     
   String rowReference = pageContext.getParameter(OAWebBeanConstants.EVENT_SOURCE_ROW_REFERENCE);
  Serializable[] params = {quoteHdrId,instanceId, rowReference };
   am.invokeMethod("validateRmaSplCopyFields",params );
    pageContext.writeDiagnostics(this, "after validateRmaSplEvent method", 1);
         }

4) below method should be created inside AM.

       public void validateRmaSplCopyFields(String quoteHdrId,
                                          String instanceId,
                                          String rowReference)
       {
 getOADBTransaction().writeDiagnostics(this," values passed quoteHdrId:"+quoteHdrId+" instanceId:"+instanceId ,1);
           if( !(quoteHdrId.equals("")) && !(instanceId.equals("")))
          {
           String typeInToner = "APPS.RAC_TONER_REC_TYPE";
           String typeTableInToner = "APPS.RAC_TONER_TBL_TYPE";
           String typeTableOutToner = "APPS.RAC_TONER_TBL_OUT_TYPE";
           String validationFlag = "N";
           Object[] outPutdata = null;
           Object[] getResults = null;
            String errMsg="n";
            String error="n";
           Struct InclRow = null;
           STRUCT[] structArr = new STRUCT[1];
           Connection conn = getOADBTransaction().getJdbcConnection();
           Number  quoteHdrIdNum = new Number(Integer.parseInt(quoteHdrId));
           Number  instanceIdNum = new Number(Integer.parseInt(instanceId));
         
           OARow row = (OARow)findRowByRef(rowReference);
           if(!(row.getAttribute("RmaSplCopies").equals("")))
           {
           Number invItemId = new Number(Integer.parseInt(row.getAttribute("UniqueValue").toString()));
           String reasonCode = (String)row.getAttribute("Recomreason");
           Number reasonCodeValue = new Number(Integer.parseInt(row.getAttribute("RmaSplCopies").toString()));
           Number orderedQuantity = new Number(Integer.parseInt(row.getAttribute("Quantity").toString()));
 try
  {
  getOADBTransaction().writeDiagnostics(this,"inside try block:",1);
  StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeInToner.toUpperCase(), conn);
ArrayDescriptor arrayDescriptor =   ArrayDescriptor.createDescriptor(typeTableInToner.toUpperCase(),conn);
               structArr[0] = new STRUCT(structDescriptor, conn, new Object[] { null,
                                                                                      invItemId,
                                                                                      reasonCode,
                                                                                      reasonCodeValue,
                                                                                      orderedQuantity,
                                                                                      null});
 ARRAY tonerInArray = new ARRAY(arrayDescriptor, conn, structArr);//IN parameter table type : RAC_TONER_TBL_TYPE
                OracleCallableStatement statement =(OracleCallableStatement)conn.prepareCall("{call RAC_IBE_EXDRECQTY_RSN_VLD_PKG.rac_toner_reason_validation_p(?,?,?,?,?)}");
                statement.setString(1, "TOPS");
                statement.setNUMBER(2, quoteHdrIdNum);
                statement.setNUMBER(3, instanceIdNum);
                statement.setArray(4, tonerInArray);
                statement.registerOutParameter(5, Types.ARRAY, typeTableOutToner);
                getOADBTransaction().writeDiagnostics(this,"statement:"+statement.toString() ,1);
                statement.execute();
                getOADBTransaction().writeDiagnostics(this,"after executing the statement:" ,1);
                outPutdata = (Object[])((java.sql.Array)statement.getObject(5)).getArray();
                getOADBTransaction().writeDiagnostics(this,"after output data:" ,1);
               
               for (Object tmp: outPutdata) {
                                InclRow = (Struct)tmp;
                                getOADBTransaction().writeDiagnostics(this,"after InclRow" ,1);
                                getResults = InclRow.getAttributes();
                                getOADBTransaction().writeDiagnostics(this,"after getResults:" ,1);
                                getOADBTransaction().writeDiagnostics(this,"Value1:"+getResults[0] ,1);
                                getOADBTransaction().writeDiagnostics(this,"Value2:"+getResults[1] ,1);
                                getOADBTransaction().writeDiagnostics(this,"Value3:"+getResults[2] ,1);
                                getOADBTransaction().writeDiagnostics(this,"Value4:"+getResults[3] ,1);
                                 validationFlag=(String)getResults[1];
                                if(validationFlag.equals("N"))
                                {
                                row.setAttribute("NewRecQty",(new Number(getResults[3])));
                                }
                                else
                                {
                                 
                                     error = (String)getResults[2];
                                }
                                InclRow = null;
                                getResults = null;
                            }
           }
           catch(Exception e)
           {
            throw new OAException("Error in validateRmaSplCopyFields method:"+e,OAException.ERROR);
           }
              if(validationFlag.equals("Y"))
              {
                      if ("RMA_INVALID".equals(error))
                       {
                           errMsg = "IKNONT_RMA_INVALID_MES";
                       }
                      else if ("RMA_USED_ALREADY".equals(error))
                       {
                           errMsg = "IKNONT_RMA_USED_ALREADY_MES";
                       }
                      else if ("RMA_EXCEEDED_QTY".equals(error))
                       {
                           errMsg = "IKNONT_RMA_EXCEEDED_QTY_MES";
                       }
                      else if ("SPLCPY_ORD_EXISTS_IN_180_DAYS".equals(error))
                       {
                           errMsg = "IKNONT_SPLCOPY_ORD_EXISTS_MES";
                       }
                      else if ("SPLCPY_EXCEEDED_QTY".equals(error))
                       {
                           errMsg = "IKNONT_SPLCOPY_EXC_QTY_MES";
                       }
                  throw new OAAttrValException(OAException.TYP_VIEW_OBJECT,
                                                                                          "IKN_InclusiveSuppliesVO1",
                                                                                          row.getKey(),
                                                                                          "ReasonCodeVal",
                                                                                          row.getAttribute("RmaSplCopies").toString(),
                                                                                          "IKNONT",
                                                                                           errMsg
                                                                                          );
              }
          }
          }
       }