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
                                                                                          );
              }
          }
          }
       }

How to enable and disable a table column based on another table column value in OAF

How to enable and disable a table column based on another table column value in OAF

 

Today I am going to tell you how to enable and disable a table column based on another table column value in OAF. E.g. We have one scenario where we have one column as recommended reason which is a drop down. There are 4  values in it. We have to fulfill below conditions:

 

1) If reason is selected as TONER from drop down, then RMA Number and Customer comments field should be disabled.

2) If reason is selected as RETURN from drop down, then RMA Number/Copies column should be enabled and required field with a tip message as "Please Enter RMA Number".

3) If reason is selected as SPLCPOY from drop down, then RMA Number/Copies column should be enabled and required field with a tip message as "Please Enter No. of Units".

4) If reason is selected as OTHERS from drop down, then customer comments column should be enabled with some default value and its required and RMA number/Copies field will be disabled.

5) If "RETURN" value selected and some values entered into the RMA number/copies field, and after that if drop down value is being changed, then RMA number/Copies field value should be cleared out and it should show the new tip for SPLCOPY only.

 

Very first we have to add 4 transient attributes to the VO as below. one is for RMA number and other for customer comments enable/disable:one is for RMA number and other for customer comments required/non required

 

 

 

 

Now First we will see how the page will be created:

1) Recommended reason property should be set as below:

 

 

 

 

2) As we have to set a tip conditionally on the same field, first we will create flow layout. see the below property:

 

3) under flowlayout, again we will create stack layout.

4) under stack layout we will create textinput item for RMA number/Copies field as below:

5) under stack layout, now we will create switcher which will be having two tip items. For switcher, we have to create different view attribute as well.

 

 

6) Now we will create two tips for RETURN and SPLCOPY case as below:

 

 

 

7) Now we have to write a logic in controller to get the reason value, after selecting the drop down value:

 

           if ("recReasonEvent".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM))) {
                       pageContext.writeDiagnostics(this, "recReasonEvent event start", 1);
                           String rowReference = pageContext.getParameter(OAWebBeanConstants.EVENT_SOURCE_ROW_REFERENCE);
                  Serializable[] paramEnableCustCommentRma = { rowReference };
                  pageContext.writeDiagnostics(this, "before enableCustCommentRma method", 1);
                  am.invokeMethod("enableCustCommentRma",paramEnableCustCommentRma);
                  pageContext.writeDiagnostics(this, "after enableCustCommentRma method", 1);
           }

 

8) Now we have to write method in AM as below:

 

       public void enableCustCommentRma(String rowReference)
       {
           Row currentRow = findRowByRef(rowReference);
           String strRecommMeaning = (String)currentRow.getAttribute("Recomreason");
          getOADBTransaction().writeDiagnostics(this,"enableCustCommentRma :strRecommMeaning=" + strRecommMeaning, 1);
                           if (!(("").equals(strRecommMeaning) &&
                                 strRecommMeaning == null)) {

                               if (("OTHER").equals(strRecommMeaning)) {
                                   currentRow.setAttribute("IsCustCommentsDisabled", false);
                                   currentRow.setAttribute("IsRmaSplCopiesDisabled", true);
                                   currentRow.setAttribute("RmaSplCopies", null);
                                   currentRow.setAttribute("CustComments", "Explain why you need more than the Recommended Quantity of the item(s) highlighted below. There is 230 character limit");
                                   currentRow.setAttribute("IsRmaNumberRequired", "no");
                                   currentRow.setAttribute("IsCustomerCmtRequired", "yes");
                               }
                               else if (("RETURN").equals(strRecommMeaning) || ("SPLCOPY").equals(strRecommMeaning)) {
                                   currentRow.setAttribute("IsCustCommentsDisabled", true);
                                   currentRow.setAttribute("IsRmaSplCopiesDisabled", false);
                                   currentRow.setAttribute("IsRmaNumberRequired", "yes");
                                   currentRow.setAttribute("IsCustomerCmtRequired", "no");
                                   currentRow.setAttribute("RmaSplCopies", null);
                                   currentRow.setAttribute("CustComments",null);
                                   if(("RETURN").equals(strRecommMeaning))
                                   {
                                   currentRow.setAttribute("RmaSplTip", "rmaTip");
                                   }
                                   else
                                   {
                                       currentRow.setAttribute("RmaSplTip", "splTip");
                                   }
                               }
                               else if (("TONER").equals(strRecommMeaning)) {
                                   currentRow.setAttribute("IsCustCommentsDisabled", true);
                                   currentRow.setAttribute("IsRmaSplCopiesDisabled", true);
                                   currentRow.setAttribute("RmaSplCopies", null);
                                   currentRow.setAttribute("CustComments",null);
                                   currentRow.setAttribute("IsRmaNumberRequired", "no");
                                   currentRow.setAttribute("IsCustomerCmtRequired", "no");
                               }
                           }
       }

9) Now we will deploy all these objects on server, and bounce the server then test the scenarios:

 

first we select the different reasons for each line and see.