1 2 Previous Next 28 Replies Latest reply: Mar 18, 2013 7:04 AM by christm31 Go to original post RSS
      • 15. Re: create_assignment_extra_info API
        barry.goodsell
        Another thing, you should always use TRUNC(SYSDATE) when comparing against any effective_start_date and effective_end_date columns, ie:

        where trunc(sysdate) between per.effective_start_date and per.effective_end_date
        and per.person_id = adr.person_id
        and adr.date_to is null
        and per.person_id = asg.person_id
        and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
        and asg.position_id = pos.position_id
        • 16. Re: create_assignment_extra_info API
          christm31
          Hi

          I'm still having issues with this (apologies for delay in replying, was off sick with kidney infection).
          Anyway, I've stripped it back to the script below where I'm hard coding an assignment and not doing any sql statement or any loop.
          I've manually inserted a row through the system for this assignment_id so I'm just replicating the values it put in when I did that.
          However, it's STILL not inserting into the table.
          I know this should be easy but no idea where I'm going wrong - it must be something stupid!

          Any ideas?

          DECLARE
          v_extra NUMBER;
          v_object NUMBER;
          v_api_error VARCHAR (500);
          v_errcode NUMBER;
          BEGIN
          hr_assignment_extra_info_api.create_assignment_extra_info
          (p_assignment_id => '7736',
          p_information_type => 'XXSAC_AUTO_ENROLMENT',
          p_aei_information_category => 'XXSAC_AUTO_ENROLMENT',
          p_assignment_extra_info_id => v_extra,
          p_object_version_number => v_object,
          p_aei_information1 => '1',
          p_aei_information2 => '18-MAR-2013',
          p_aei_information3 => 'Test' );


          COMMIT;
          EXCEPTION
          WHEN OTHERS
          THEN
          v_api_error := SQLERRM;
          v_errcode := SQLCODE;
          END;

          Thanks

          Martin
          • 17. Re: create_assignment_extra_info API
            clive_t
            Hi Martin

            No error message?

            What happens if you put that date parameter value in as '2013/03/18 00:00:00' ?

            Can you amend it to put the following immediately after the api call:

            dbms_output.put_line('Returned id: '||TO_CHAR(v_extra));

            Clive
            • 18. Re: create_assignment_extra_info API
              christm31
              Hi

              It's made no difference and I'm not getting anything in my DBMS_OUTPUT window.
              Interestingly, if you go back to my original post, you'll see that I have a working version of this for information_type 'XXSAC_PAYROLL_NOTIFICATION_DET'.
              If I amend that existing working API just to change the information_type to 'XXSAC_INITIAL_ASSESSMENT', it stops working and doesn't input anything.
              However, if I amend the one I'm working on now to have an information_type of 'XXSAC_PAYROLL_NOTIFICATION_DET' instead, it still doesn't work.

              Not sure what that tells me though :-)

              Martin
              • 19. Re: create_assignment_extra_info API
                clive_t
                When you say 'I'm not getting anything in my DBMS_OUTPUT window', do you mean literally nothing, or are you seeing something like 'Returned id: ' but with no number after it?
                • 20. Re: create_assignment_extra_info API
                  christm31
                  Nothing at all - just an empty screen
                  • 21. Re: create_assignment_extra_info API
                    clive_t
                    Aha! Are you running this process through TOAD or some other sql environment, or as a concurrent request via the application?
                    • 22. Re: create_assignment_extra_info API
                      Sanjay Singh
                      Hi Martin,

                      Can you please check that the EIT in which you are trying to insert data whether its been registerd for having single record ? this is set as a property when you are registering the EIT it asks for whether Multiple rows (Y/N), you can check the value selected while registering in table PER_PEOPLE_INFO_TYPES

                      Query - select multiple_occurences_flag from PER_PEOPLE_INFO_TYPES where information_type = <your EIT name>

                      IF the output of the query is "N" then update it to "Y" and re-try.

                      Thanks,
                      Sanjay
                      • 23. Re: create_assignment_extra_info API
                        christm31
                        Hi

                        Running it through TOAD.

                        When I run that query there are no rows returned.

                        Martin
                        • 24. Re: create_assignment_extra_info API
                          clive_t
                          OK, in TOAD, in the sql window where you have your code, you will hopefully see a tab in the bottom part of the screen (where you see the results of queries etc) labelled 'DBMS Output' - does it say '(disabled)'? If so, click on the tab and click the red dot on the left of that part of the screen, until it becomes green. Then run your process again.

                          PS You might also want to put this as the first line in your exception:

                          dbms_output.put_line(substr(sqlerrm,1,200));

                          Edited by: clive_t on 18-Mar-2013 13:17
                          • 25. Re: create_assignment_extra_info API
                            christm31
                            Hi

                            Yes I have the DBMS output window opened and it has a green button at the top left but it's showing an empty screen

                            Martin
                            • 26. Re: create_assignment_extra_info API
                              clive_t
                              What about if you have that other dbms_output statement in your exception 'WHEN OTHERS' clause?
                              • 27. Re: create_assignment_extra_info API
                                christm31
                                Just as a bizarre update.
                                I ran the script twice there and both times it inserted the row I was expecting,
                                I've just ran the very same script another 3 times and it's not inserted anything any of those 3 times.
                                :-(

                                Martin
                                • 28. Re: create_assignment_extra_info API
                                  christm31
                                  Update

                                  It now seems to be working consistently - I've honestly no idea why it's suddenly working but it is!

                                  Thanks

                                  Martin
                                  1 2 Previous Next