This discussion is archived
9 Replies Latest reply: Apr 8, 2013 8:58 AM by Andreas Weiden RSS

frm-40735 ora-06502

faisal niazi-2368916 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points