4 Replies Latest reply: Sep 19, 2013 12:28 AM by Aravinda RSS

    How to call pl/sql function from oaf

    Aravinda

      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

          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

            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

              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

                Thanks a lot Dilip..It worked