9 Replies Latest reply: Apr 8, 2013 10:58 AM by Andreas Weiden RSS

    frm-40735 ora-06502

    faisal niazi-2368916
      Hello Guys.

      I am using API to upload the element entry in the Oracle HRMS , 12.1.3 below is my pl sql code, this code is giving me the error

      frm-40735 ora-06502
      PROCEDURE UPLOAD_ELEMENT IS      
      --
      l_effective_start_date   date     ;
      l_effective_end_date     date     ;
      l_element_entry_id       number   ;
      l_object_version_number  number   ;
      l_create_warning         boolean  ;
      lhours number;
      ehours number;
      err_num  number;
      err_msg varchar2(100);
      
      Begin
      For csr_emp in (select * from emp_attendance)
                       loop
                            
                            /* Assignment */
      --For csr_asg in (Select * from apps.per_all_assignments_f 
      --       where person_id = csr_emp.emp_id       
      --       and sysdate between effective_start_date and effective_end_date) loop
      
           
           For csr_asg in (Select * from apps.per_all_assignments_f 
             where assignment_number = csr_emp.emp_id) 
             loop
      
            select late_hours into lhours from emp_attendance 
            where emp_id =csr_emp.emp_id;       
            
           select early_hours into ehours from emp_attendance 
           where emp_id =csr_emp.emp_id;       
      --
      APPS.pay_element_entry_api.create_element_entry
        (p_validate                    =>    false
        ,p_effective_date              =>    '27-MAR-2012'
        ,p_business_group_id           =>    fnd_profile.value('PER_BUSINESS_GROUP_ID') 
        --,p_assignment_id               =>    csr_asg.assignment_id
        ,p_assignment_id               =>    csr_emp.emp_id
        ,p_element_link_id             =>    3122
        ,p_entry_type                  =>    'E'
        ,p_creator_type                =>    'F'
        ,p_input_value_id1             =>    407
        ,p_input_value_id2             =>    408  
        ,p_entry_value1                =>    lhours
        ,p_entry_value2                =>    ehours
        ,p_effective_start_date             => l_effective_start_date 
        ,p_effective_end_date               => l_effective_end_date   
        ,p_element_entry_id                 => l_element_entry_id     
        ,p_object_version_number            => l_object_version_number
        ,p_create_warning                   => l_create_warning       
        );  
      End loop;
      End loop;
      EXCEPTION
      WHEN OTHERS THEN
      err_num := SQLCODE;
      err_msg := SUBSTR(SQLERRM, 1, 100);
      message (err_num, err_msg);
      message (err_num, err_msg);
      
      End;
      Any help will be appreciated.
        • 1. Re: frm-40735 ora-06502
          998557
          Check whether all the parameters and their data types are correct or not. "APPS.pay_element_entry_api.create_element_entry" - check the input parameter data type for this package.

          Thanks
          Pavan
          • 2. Re: frm-40735 ora-06502
            faisal niazi-2368916
            I have checked the package for all the required parameters and data types, could not find any thing which could create problem. below is the code for create_element_entry
            13 PROCEDURE create_element_entry
            14   (p_validate                      in     boolean  default false
            15   ,p_effective_date                in     date
            16   ,p_business_group_id             in     number
            17   ,p_original_entry_id             in     number   default null
            18   ,p_assignment_id                 in     number
            19   ,p_element_link_id               in     number
            20   ,p_entry_type                    in     varchar2
            21   ,p_creator_type                  in     varchar2 default 'F'
            22   ,p_cost_allocation_keyflex_id    in     number   default null
            23   ,p_updating_action_id            in     number   default null
            24   ,p_updating_action_type          in     varchar2 default null
            25   ,p_comment_id                    in     number   default null
            26   ,p_reason                        in     varchar2 default null
            27   ,p_target_entry_id               in     number   default null
            28   ,p_subpriority                   in     number   default null
            29   ,p_date_earned                   in     date     default null
            30   ,p_personal_payment_method_id    in     number   default null
            31   ,p_attribute_category            in     varchar2 default null
            32   ,p_attribute1                    in     varchar2 default null
            33   ,p_attribute2                    in     varchar2 default null
            34   ,p_attribute3                    in     varchar2 default null
            35   ,p_attribute4                    in     varchar2 default null
            36   ,p_attribute5                    in     varchar2 default null
            37   ,p_attribute6                    in     varchar2 default null
            38   ,p_attribute7                    in     varchar2 default null
            39   ,p_attribute8                    in     varchar2 default null
            40   ,p_attribute9                    in     varchar2 default null
            41   ,p_attribute10                   in     varchar2 default null
            42   ,p_attribute11                   in     varchar2 default null
            43   ,p_attribute12                   in     varchar2 default null
            44   ,p_attribute13                   in     varchar2 default null
            45   ,p_attribute14                   in     varchar2 default null
            46   ,p_attribute15                   in     varchar2 default null
            47   ,p_attribute16                   in     varchar2 default null
            48   ,p_attribute17                   in     varchar2 default null
            49   ,p_attribute18                   in     varchar2 default null
            50   ,p_attribute19                   in     varchar2 default null
            51   ,p_attribute20                   in     varchar2 default null
            52   ,p_input_value_id1               in     number   default null
            53   ,p_input_value_id2               in     number   default null
            54   ,p_input_value_id3               in     number   default null
            55   ,p_input_value_id4               in     number   default null
            56   ,p_input_value_id5               in     number   default null
            57   ,p_input_value_id6               in     number   default null
            58   ,p_input_value_id7               in     number   default null
            59   ,p_input_value_id8               in     number   default null
            60   ,p_input_value_id9               in     number   default null
            61   ,p_input_value_id10              in     number   default null
            62   ,p_input_value_id11              in     number   default null
            63   ,p_input_value_id12              in     number   default null
            64   ,p_input_value_id13              in     number   default null
            65   ,p_input_value_id14              in     number   default null
            66   ,p_input_value_id15              in     number   default null
            67   ,p_entry_value1                  in     varchar2 default null
            68   ,p_entry_value2                  in     varchar2 default null
            69   ,p_entry_value3                  in     varchar2 default null
            70   ,p_entry_value4                  in     varchar2 default null
            71   ,p_entry_value5                  in     varchar2 default null
            72   ,p_entry_value6                  in     varchar2 default null
            73   ,p_entry_value7                  in     varchar2 default null
            74   ,p_entry_value8                  in     varchar2 default null
            75   ,p_entry_value9                  in     varchar2 default null
            76   ,p_entry_value10                 in     varchar2 default null
            77   ,p_entry_value11                 in     varchar2 default null
            78   ,p_entry_value12                 in     varchar2 default null
            79   ,p_entry_value13                 in     varchar2 default null
            80   ,p_entry_value14                 in     varchar2 default null
            81   ,p_entry_value15                 in     varchar2 default null
            82   ,p_entry_information_category    in     varchar2 default null
            83   ,p_entry_information1            in     varchar2 default null
            84   ,p_entry_information2            in     varchar2 default null
            85   ,p_entry_information3            in     varchar2 default null
            86   ,p_entry_information4            in     varchar2 default null
            87   ,p_entry_information5            in     varchar2 default null
            88   ,p_entry_information6            in     varchar2 default null
            89   ,p_entry_information7            in     varchar2 default null
            90   ,p_entry_information8            in     varchar2 default null
            91   ,p_entry_information9            in     varchar2 default null
            92   ,p_entry_information10           in     varchar2 default null
            93   ,p_entry_information11           in     varchar2 default null
            94   ,p_entry_information12           in     varchar2 default null
            95   ,p_entry_information13           in     varchar2 default null
            96   ,p_entry_information14           in     varchar2 default null
            97   ,p_entry_information15           in     varchar2 default null
            98   ,p_entry_information16           in     varchar2 default null
            99   ,p_entry_information17           in     varchar2 default null
            100   ,p_entry_information18           in     varchar2 default null
            101   ,p_entry_information19           in     varchar2 default null
            102   ,p_entry_information20           in     varchar2 default null
            103   ,p_entry_information21           in     varchar2 default null
            104   ,p_entry_information22           in     varchar2 default null
            105   ,p_entry_information23           in     varchar2 default null
            106   ,p_entry_information24           in     varchar2 default null
            107   ,p_entry_information25           in     varchar2 default null
            108   ,p_entry_information26           in     varchar2 default null
            109   ,p_entry_information27           in     varchar2 default null
            110   ,p_entry_information28           in     varchar2 default null
            111   ,p_entry_information29           in     varchar2 default null
            112   ,p_entry_information30           in     varchar2 default null
            113   ,p_override_user_ent_chk         in     varchar2 default 'N'
            114   ,p_effective_start_date             out nocopy date
            115   ,p_effective_end_date               out nocopy date
            116   ,p_element_entry_id                 out nocopy number
            117   ,p_object_version_number            out nocopy number
            118   ,p_create_warning                   out nocopy boolean
            119   ) IS
            Edited by: Ogrey on Apr 1, 2013 4:32 AM
            • 3. Re: frm-40735 ora-06502
              998557
              Check whether the below fields are returning correct values as per the input data type.

              fnd_profile.value('PER_BUSINESS_GROUP_ID')
              p_entry_value1 => lhours
              p_entry_value2 => ehours
              • 4. Re: frm-40735 ora-06502
                faisal niazi-2368916
                yes, all these are returning proper values
                fnd_profile.value('PER_BUSINESS_GROUP_ID') =81
                p_entry_value1 => lhours =43469
                p_entry_value2 => ehours =0

                i even hard coded these values but still the result is same.
                • 5. Re: frm-40735 ora-06502
                  HamidHelal
                  Ogrey wrote:
                  yes, all these are returning proper values
                  fnd_profile.value('PER_BUSINESS_GROUP_ID') =81
                  p_entry_value1 => lhours =43469
                  p_entry_value2 => ehours =0

                  i even hard coded these values but still the result is same.
                  debug your code by writing message(); and trap which portion of code are not executed.. like
                  ---------
                  code
                  ---------
                  message('1');
                  message('1');
                  -----------
                  code
                  ---------
                  message('2');
                  message('2');
                  --------
                  hope this will helps...

                  Hamid

                  Mark correct/helpful to help others to get right answer(s).*
                  • 6. Re: frm-40735 ora-06502
                    998557
                    You need to debug your code, otherthan this there is no other way to identify the issue.

                    Thanks
                    Pavan
                    • 7. Re: frm-40735 ora-06502
                      faisal niazi-2368916
                      PROCEDURE UPLOAD_ELEMENT IS      
                      --
                      l_effective_start_date   date     ;
                      l_effective_end_date     date     ;
                      l_element_entry_id       number   ;
                      l_object_version_number  number   ;
                      l_create_warning         boolean  ;
                      lhours number;
                      ehours number;
                      err_num  number;
                      err_msg varchar2(100);
                      Begin
                      message('1');
                      message('1');
                      For csr_emp in (select * from emp_attendance)
                                       loop
                      --For csr_asg in (Select * from apps.per_all_assignments_f 
                      --       where person_id = csr_emp.emp_id       
                      --       and sysdate between effective_start_date and effective_end_date) loop     
                           For csr_asg in (Select * from apps.per_all_assignments_f 
                             where assignment_number = '1452010') 
                             loop
                                  message('2');
                                     message('2');
                            select late_hours into lhours from emp_attendance 
                            where emp_id =csr_emp.emp_id;
                           select early_hours into ehours from emp_attendance 
                           where emp_id =csr_emp.emp_id;     
                           message('3');
                      message('3');
                      APPS.pay_element_entry_api.create_element_entry
                        (p_validate                    =>    false
                        ,p_effective_date              =>    to_date(sysdate)  --'27-MAR-2012'
                        ,p_business_group_id           =>     81      --fnd_profile.value('PER_BUSINESS_GROUP_ID') 
                        --,p_assignment_id               =>    csr_asg.assignment_id
                        ,p_assignment_id               =>    csr_emp.emp_id
                        ,p_element_link_id             =>    3122
                        ,p_entry_type                  =>    'E'
                        ,p_creator_type                =>    'F'
                        ,p_input_value_id1             =>    407
                        ,p_input_value_id2             =>    408  
                        ,p_entry_value1                =>    '400'
                        ,p_entry_value2                =>    '300'
                        ,p_effective_start_date             => l_effective_start_date 
                        ,p_effective_end_date               => l_effective_end_date   
                        ,p_element_entry_id                 => l_element_entry_id     
                        ,p_object_version_number            => l_object_version_number
                        ,p_create_warning                   => l_create_warning       
                        ,p_override_user_ent_chk   =>  'N'
                        );  
                        message('4');
                      message('4');
                             End loop;
                      End loop;
                        message('5');
                      message('5');
                      EXCEPTION
                      WHEN OTHERS THEN
                      err_num := SQLCODE;
                      err_msg := SUBSTR(SQLERRM, 1, 100);
                      message (err_num, err_msg);
                      message (err_num, err_msg);
                      End;
                      After making some changes and trying to debug the code, the Message for 1, 2 and 3 are displayed while message box for 4 , 5 are not showing up, any help what could be the reason. and one more thing , message for 1 , and 2 are displayed twice while for 3 its displayed once.


                      Regards

                      Edited by: Ogrey on Apr 7, 2013 11:56 PM
                      • 8. Re: frm-40735 ora-06502
                        HamidHelal
                        Ogrey wrote:
                        PROCEDURE UPLOAD_ELEMENT IS      
                        --
                        l_effective_start_date   date     ;
                        l_effective_end_date     date     ;
                        l_element_entry_id       number   ;
                        l_object_version_number  number   ;
                        l_create_warning         boolean  ;
                        lhours number;
                        ehours number;
                        err_num  number;
                        err_msg varchar2(100);
                        Begin
                        message('1');
                        message('1');
                        For csr_emp in (select * from emp_attendance)
                        loop
                        --For csr_asg in (Select * from apps.per_all_assignments_f 
                        --       where person_id = csr_emp.emp_id       
                        --       and sysdate between effective_start_date and effective_end_date) loop     
                             For csr_asg in (Select * from apps.per_all_assignments_f 
                               where assignment_number = '1452010') 
                               loop
                                    message('2');
                                       message('2');
                              select late_hours into lhours from emp_attendance 
                              where emp_id =csr_emp.emp_id;
                             select early_hours into ehours from emp_attendance 
                             where emp_id =csr_emp.emp_id;     
                             message('3');
                        message('3');
                        APPS.pay_element_entry_api.create_element_entry
                        (p_validate                    =>    false
                        ,p_effective_date              =>    to_date(sysdate)  --'27-MAR-2012'
                        ,p_business_group_id           =>     81      --fnd_profile.value('PER_BUSINESS_GROUP_ID') 
                        --,p_assignment_id               =>    csr_asg.assignment_id
                        ,p_assignment_id               =>    csr_emp.emp_id
                        ,p_element_link_id             =>    3122
                        ,p_entry_type                  =>    'E'
                        ,p_creator_type                =>    'F'
                        ,p_input_value_id1             =>    407
                        ,p_input_value_id2             =>    408  
                        ,p_entry_value1                =>    '400'
                        ,p_entry_value2                =>    '300'
                        ,p_effective_start_date             => l_effective_start_date 
                        ,p_effective_end_date               => l_effective_end_date   
                        ,p_element_entry_id                 => l_element_entry_id     
                        ,p_object_version_number            => l_object_version_number
                        ,p_create_warning                   => l_create_warning       
                        ,p_override_user_ent_chk   =>  'N'
                        );  
                        message('4');
                        message('4');
                               End loop;
                        End loop;
                        message('5');
                        message('5');
                        EXCEPTION
                        WHEN OTHERS THEN
                        err_num := SQLCODE;
                        err_msg := SUBSTR(SQLERRM, 1, 100);
                        message (err_num, err_msg);
                        message (err_num, err_msg);
                        End;
                        After making some changes and trying to debug the code, the Message for 1, 2 and 3 are displayed while message box for 4 , 5 are not showing up, any help what could be the reason. and one more thing , message for 1 , and 2 are displayed twice while for 3 its displayed once.


                        Regards
                        Hello Ogrey
                        message 2 are in loop so it may show many times.
                        message 4 will display after success of APPS.pay_element_entry_api.create_element_entry but it faild so check the APPS.pay_element_entry_api.create_element_entry with the value return by first loop.

                        Hope this helps

                        Hamid
                        • 9. Re: frm-40735 ora-06502
                          Andreas Weiden
                          p_effective_date is a date, but you pass a VARCHAR2 in your first call, and have an implicit Cast to a VARCHAR2 when you use TO_DATE(SYSDATE). SYSDATE is already a date, to leave out the TO_DATE.