This discussion is archived
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Nothing at all - just an empty screen
  • 21. Re: create_assignment_extra_info API
    clive_t Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    What about if you have that other dbms_output statement in your exception 'WHEN OTHERS' clause?
  • 27. Re: create_assignment_extra_info API
    christm31 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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