This discussion is archived
4 Replies Latest reply: Sep 18, 2013 10:28 PM by Aravinda RSS

How to call pl/sql function from oaf

Aravinda Newbie
Currently Being Moderated

Dear  Friends,

 

I am trying to call a function from oaf page .but iam getting error like

 

java.sql.SQLException: ORA-01861: literal does not match format string

ORA-06512: at line 1

 

 

 

THis is my function  ,,callable statement

 

FUNCTION CAR_LOAN_VALIDATION(P_PERSON_ID IN NUMBER

                                ,P_DEDUCTION_START_DATE IN DATE

                                ,P_DEDUCTION_END_DATE IN DATE

                                ,P_LOAN_VALUE IN NUMBER

                                )

   RETURN VARCHAR2

   IS

      l_message                 VARCHAR2(100);

      l_contract_type           VARCHAR2(50);

      l_loan_eligible_date      DATE;

      l_grade                   VARCHAR2(15);

      l_old_loan_end_date       DATE;

      l_max_loan_end_date       DATE;

      l_remaining_amount        NUMBER;

      l_eligible_amt            NUMBER;

   BEGIN

 

 

 

        BEGIN

            SELECT --papf.person_id,

                   (SELECT meaning

                      FROM hr_lookups

                     WHERE lookup_type = 'ADTC_CONTRACT_TYPE'

                       AND lookup_code = attribute3) contract_type,

                   ADD_MONTHS (papf.original_date_of_hire, 11) loan_eligible_date,

                   DECODE (paaf.ass_attribute2,

                           'Chairman', 'Chairman',

                           SUBSTR (ass_attribute2, INSTR (ass_attribute2, '.', 1, 1) + 1,

                                   3)

                          ) grade,

                   (SELECT nvl(MAX (deduction_end_date),null)

                      FROM xxadtc_car_loan_request

                     WHERE person_id = p_person_id) old_loan_end_date,

                   to_date(ADD_MONTHS (p_deduction_start_date, 47)) max_loan_end_date

                   --to_date(ADD_MONTHS (sysdate, 47)) max_loan_end_date

              INTO  l_contract_type

                    ,l_loan_eligible_date

                    ,l_grade

                    ,l_old_loan_end_date

                    ,l_max_loan_end_date

              FROM per_all_people_f papf, per_all_assignments_f paaf

             WHERE SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

               AND papf.business_group_id = 81

               AND papf.person_id = paaf.person_id

               AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date

               AND papf.person_id =P_PERSON_ID;

        EXCEPTION

            WHEN others THEN

                DBMS_OUTPUT.PUT_LINE('Error ');

        END;

 

 

        -- get Previous Car loan amount --

        BEGIN

            SELECT to_number(prrv.result_value)

              INTO l_remaining_amount

              FROM per_all_people_f papf,

                   per_all_assignments_f paaf,

                   pay_assignment_actions pac,

                   pay_run_results prr,

                   pay_run_result_values prrv,

                   pay_element_types_f petf,

                   pay_input_values_f pivf,

                   pay_element_classifications pec,

                   pay_payroll_actions ppa,

                   per_time_periods ptp

             WHERE SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

               AND papf.person_id = paaf.person_id

               AND papf.business_group_id = 81

               AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date

               AND paaf.assignment_id = pac.assignment_id

               AND pac.assignment_action_id = prr.assignment_action_id

               AND prr.run_result_id = prrv.run_result_id

               AND prr.element_type_id = petf.element_type_id

               AND SYSDATE BETWEEN petf.effective_start_date AND petf.effective_end_date

               AND prrv.input_value_id = pivf.input_value_id

               AND SYSDATE BETWEEN pivf.effective_start_date AND pivf.effective_end_date

               AND pivf.NAME = 'Remaining Amount'

               AND petf.classification_id = pec.classification_id

               AND pac.payroll_action_id = ppa.payroll_action_id

               AND ppa.time_period_id = ptp.time_period_id

               AND TO_CHAR (ptp.pay_advice_date, 'Mon-YYYY') =

                                                             TO_CHAR (SYSDATE, 'Mon-YYYY')

               AND petf.element_name = 'Motar Ahlami Loan'

            --and     employee_number='10185'

               AND papf.person_id = p_person_id;

        EXCEPTION

            WHEN no_data_found THEN

               --DBMS_OUTPUT.PUT_LINE('ERROR: No data!');

               l_remaining_amount:=0;

            WHEN others THEN

                DBMS_OUTPUT.PUT_LINE('Error ');              

        END;

          

           select   GET_CAR_LOAN_ELIGI_AMT(P_PERSON_ID)

           into     l_eligible_amt

           from     dual;

          

           IF l_contract_type NOT IN ('Permanent') THEN

                l_message := 'Contract Type Is not valid';

           END IF;

           IF l_loan_eligible_date < SYSDATE THEN

                l_message:= 'Date of Join is less then one year';

           END IF;

           IF l_grade IN ('US','00','AUS','EM') THEN

                l_message:= 'Your are not Eligible Grade';

           END IF;

          

           IF l_old_loan_end_date < SYSDATE THEN

                l_message:= 'Previous Loan Duration is not completed';

           END IF;

          

           IF l_max_loan_end_date > P_DEDUCTION_END_DATE THEN

                l_message:= 'Maxinum number of monthly payment of 48 month';

           END IF;

          

           IF l_remaining_amount >0 THEN

                l_message:= 'Previous Car Loan amount is pending till';

           END IF;

          

           IF l_eligible_amt < p_loan_value THEN

                l_message:= 'Please check Eligible Amount ';

           END IF;

 

 

      RETURN nvl(l_message,'S');

   EXCEPTION

      WHEN NO_DATA_FOUND

      THEN

         RETURN 'Error';

   END CAR_LOAN_VALIDATION;

END XX_CAR_LOAN_REQUEST_PKG;

 

 

 

CALLABLE STATEMENT CODE ---CO CODE

 

      if(pageContext.getParameter("Submit")!=null) {

         OAViewObject vo2 = (OAViewObject)am.findViewObject("XxadtcCarLoanRequestEOVO1");

            if(vo1!=null){

               vo1.getCurrentRow().setAttribute("LoanStatus","Pending Approval");

               int xxpersonId = pageContext.getEmployeeId();

              

               String xxDeductionStartDate = vo2.getCurrentRow().getAttribute("DeductionStartDate").toString();//pageContext.getParameter("DeductionStartDate").toString();

//                SimpleDateFormat dateFormat1 = new SimpleDateFormat("yyyy/mm/dd");

//                Date convertedDate1 = dateFormat1.parse("xxDeductionStartDate");

              

               String xxDeductionEndDate = vo2.getCurrentRow().getAttribute("DeductionEndDate").toString();//pageContext.getParameter("DeductionEndDate").toString();

//                SimpleDateFormat dateFormat2 = new SimpleDateFormat("yyyy/mm/dd");

//                Date convertedDate2 = dateFormat2.parse("xxDeductionEndDate");

              

              

              

               String xxLoanValue = vo2.getCurrentRow().getAttribute("LoanValue").toString();//pageContext.getParameter("LoanValue").toString();

                int iLoanValue = Integer.parseInt(xxLoanValue);

                System.out.println("Entered into submit loop");

                System.out.println("xxpersonId id is:"+xxpersonId);

                System.out.println("xxDeductionStartDate id is:"+xxDeductionStartDate);

                System.out.println("xxDeductionEndDate id is:"+xxDeductionEndDate);

                System.out.println("xxLoanValue id is:"+xxLoanValue);

              OADBTransaction txn = am.getOADBTransaction();

              String sql ="BEGIN :1 :=XXADTC_CAR_LOAN_REQUEST_PKG.CAR_LOAN_VALIDATION(:2,:3,:4,:5); END;";

              CallableStatement cs =  txn.createCallableStatement(sql,1);

              String outputval = null;//  int outputval = 0;

              try {

                  System.out.println("Entered in try loop of callable funtion validation");

              cs.setInt(2,xxpersonId);

                    System.out.println("cs setInt(2,xxpersonId);is executed");

              cs.setString(3,xxDeductionStartDate);

                    System.out.println("cs.setString(3,xxDeductionStartDate is executed);");

              cs.setString(4,xxDeductionEndDate);

                    System.out.println("cs.setString(4,xxDeductionEndDate is executed);");

//                              cs.setDate(3,xxDeductionStartDate);

//                              cs.setDate(4,xxDeductionEndDate);

              cs.setInt(5,iLoanValue);

              cs.registerOutParameter(1,Types.VARCHAR);//  cs.registerOutParameter(1,Types.NUMERIC);

              cs.execute();

              outputval = cs.getString(1);// txn.commit();

               System.out.println(outputval);

                    txn.commit();

                        if(outputval.equals("S"))

                        {

                        am.invokeMethod("apply");

                        }

                        else

                        {

                         throw new OAException("Organization name is:"+outputval,OAException.INFORMATION);

                        }

                    txn.commit();

                    }

                    catch(Exception sqle){

                    try {

                    cs.close();

                    }

                    catch (Exception e)

                    {

                    throw OAException.wrapperException(e);

                    }

                    throw OAException.wrapperException(sqle);

                                          }

                    }               

                   

 

 

Please help .where is the error is.

please help

 

let me know for any clarification.

 

Thanks

Aravinda.

  • 1. Re: How to call pl/sql function from oaf
    DilipG Journeyer
    Currently Being Moderated

    Hi Arvinda,

     

    The date Field has always been a issue in OAF framework. The best way to handle the date in the insert is like to_date(to_char(sysdate,'DD-MM-YYYY'),'DD-MM-YYYY') .

     

     

      This above mentioned issue could be happening due to the difference in the date format of the database and jdeveloper connection .

     

    Thanks,

    Dilip

  • 2. Re: How to call pl/sql function from oaf
    Aravinda Newbie
    Currently Being Moderated

    Hi Dilip,

     

    Thanks to confirming the error.But what to do now

    Iam sorry ,I did not get ur answer  "The best way to handle the date in the insert is like to_date(to_char(sysdate,'DD-MM-YYYY'),'DD-MM-YYYY') "

    I did not use any INSERT .

    I used VO for select statements and CO for callable statement.. where I have to change ..what to change.  ...Please help

    IN CO code if the the fuction return value is "S"., what ever the changes done by the user will get commited.

     

    let me know for any clarification.

     

     

     

    Thanks

    Aravinda

  • 3. Re: How to call pl/sql function from oaf
    DilipG Journeyer
    Currently Being Moderated

    Hi Arvinda,

     

     

    Refer Below code.

     

     

     

    // In Controller

     

    java.sql.Date DeductionSDate; //declare variable

     

    //In PFR

     

      String xxDeductionStartDate = vo2.getCurrentRow().getAttribute("DeductionStartDate");

       OADBTransaction txn = am.getOADBTransaction();

         if(xxDeductionStartDate!=null)

              {

                java.sql.Date startDate= txn.getOANLSServices().stringToDate(xxDeductionStartDate);

                DeductionSDate = startDate;

              }

     

     

     

        Set this to cs.setDate(3,DeductionSDate);


    Thanks,

    Dilip

     

  • 4. Re: How to call pl/sql function from oaf
    Aravinda Newbie
    Currently Being Moderated

    Thanks a lot Dilip..It worked