This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Mar 18, 2013 7:04 AM by christm31 RSS

create_assignment_extra_info API

christm31 Newbie
Currently Being Moderated
Hi

Hoping someone can help. I've created 2 different scripts to call this API. One successfully creates the records and the other doesn't.
The one that doesn't, doesn't give any messages - it says completed successfully, but when I check the table, there is nothing in it.
I can't really see why one would work and the other wouldn't.
They're both posted below - can anyone tell me why the 2nd one wouldn't work when the first one does? The cursor in the 2nd script is definitely bringing back data.

Thanks

Martin


-----The script that works:
DECLARE
v_person_id VARCHAR (100);
v_assignment_id NUMBER;
v_extra NUMBER;
v_object NUMBER;
v_api_error VARCHAR (500);
v_errcode NUMBER;
CURSOR c1
IS
SELECT *
FROM XXSAC_PAYROLL_INITIAL
WHERE employee_number = '7294197' ;
BEGIN
FOR rec IN c1
LOOP
BEGIN
SELECT DISTINCT person_id
INTO v_person_id
FROM per_all_people_f a
WHERE employee_number = rec.employee_number
AND sysdate between effective_start_date and effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT assignment_id
INTO v_assignment_id
FROM per_all_assignments_f
WHERE person_id = v_person_id AND sysdate between effective_start_date and effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
hr_assignment_extra_info_api.create_assignment_extra_info
(p_assignment_id => v_assignment_id,
p_information_type => 'XXSAC_PAYROLL_NOTIFICATION_DET',
p_aei_information_category => 'XXSAC_PAYROLL_NOTIFICATION_DET',

p_assignment_extra_info_id => v_extra,
p_object_version_number => v_object,
p_aei_information20 => rec.UNKNOWN_1 );


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



--The script that doesn't work:
DECLARE
v_assignment_id NUMBER;
v_extra NUMBER;
v_object NUMBER;
v_api_error VARCHAR (500);
v_errcode NUMBER;

CURSOR c1
IS
select asg.assignment_id
from per_all_people_f per, per_all_assignments_f asg, per_all_positions pos, per_position_definitions def, per_addresses adr, per_assignment_extra_info paei, xxsac_pension_ees ees
where 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 sysdate between asg.effective_start_date and asg.effective_end_date
and asg.position_id = pos.position_id
and pos.date_end is null
and pos.position_definition_id = def.position_definition_id
and def.end_date_active is null
and asg.assignment_id = paei.assignment_id
and paei.information_type = 'XXSAC_PAYROLL_NOTIFICATION_DET'
and per.employee_number = ees.employee_number
and paei.aei_information20 = ees.job_no;

BEGIN
FOR rec IN c1
LOOP
BEGIN
SELECT assignment_id
INTO v_assignment_id
FROM per_all_assignments_f
WHERE assignment_id = rec.assignment_id;
EXCEPTION
WHEN OTHERS
THEN
null;
END;
BEGIN
hr_assignment_extra_info_api.create_assignment_extra_info
(p_assignment_id => v_assignment_id,
p_information_type => 'XXSAC_INITIAL_ASSESSMENT',
p_aei_information_category => 'XXSAC_INITIAL_ASSESSMENT',
p_assignment_extra_info_id => v_extra,
p_object_version_number => v_object,
p_aei_information1 => '1',
p_aei_information2 => sysdate );


COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_api_error := SQLERRM;
v_errcode := SQLCODE;
END;
END LOOP;
END;
  • 1. Re: create_assignment_extra_info API
    clive_t Pro
    Currently Being Moderated
    Hi Martin - can you put some sort of 'dbms_output' type message just before the api call, and run it again to see if it outputs that message?

    Clive
  • 2. Re: create_assignment_extra_info API
    christm31 Newbie
    Currently Being Moderated
    Hi Clive

    Thanks for the reply.
    When I change the code to the following:
    BEGIN
    dbms_output.put_line('Test Message');
    hr_assignment_extra_info_api.create_assignment_extra_info
    (p_assignment_id => v_assignment_id,
    p_information_type => 'XXSAC_INITIAL_ASSESSMENT',
    p_aei_information_category => 'XXSAC_INITIAL_ASSESSMENT',
    p_assignment_extra_info_id => v_extra,
    p_object_version_number => v_object,
    p_aei_information1 => '1',
    p_aei_information2 => sysdate );

    I don't get any message when executing it in TOAD.
    I can't see why though.

    Martin
  • 3. Re: create_assignment_extra_info API
    clive_t Pro
    Currently Being Moderated
    What's your session language setting?

    SELECT userenv('LANG') from dual;


    Edited to add: Looks to me like it's not finding anything in the cursor to work on?

    Edited by: clive_t on 05-Mar-2013 14:19
  • 4. Re: create_assignment_extra_info API
    Sanjay Singh Pro
    Currently Being Moderated
    Hi,

    The issue seems to be in the below query:

    SELECT assignment_id
    INTO v_assignment_id
    FROM per_all_assignments_f
    WHERE assignment_id = rec.assignment_id;

    It might be going in to exception for cases where there are multiple assignment records and in exception you are doing nothing (null), better you use the below query to avoid any issues and check:

    SELECT assignment_id
    INTO v_assignment_id
    FROM per_all_assignments_f
    WHERE assignment_id = rec.assignment_id
    and trunc(sysdate) between effective start_date and effective_end_date;

    Hope it helps.

    Thanks,
    Sanjay
  • 5. Re: create_assignment_extra_info API
    christm31 Newbie
    Currently Being Moderated
    SELECT userenv('LANG') from dual; returns the value 'US'

    Thanks for other reply as well - because I was doing sysdate between effective dates in the cursor I didn't think I had to do it again but will try with it in it.

    Martin
  • 6. Re: create_assignment_extra_info API
    christm31 Newbie
    Currently Being Moderated
    When I copy in the code listed here I get:

    Error at line 1
    ORA-06550: line 34, column 41:
    PL/SQL: ORA-00905: missing keyword
    ORA-06550: line 30, column 10:
    PL/SQL: SQL Statement ignored
  • 7. Re: create_assignment_extra_info API
    clive_t Pro
    Currently Being Moderated
    It begs the question why you are attempting to retrieve the same information a second time? Surely you already have the assignment_id from the driving query that's in the FOR rec in .. LOOP ?

    Indeed, the implicit cursor (identified by the INTO keyword) will expect to find exactly one row. If none, or more than 1, it will raise an exception.

    If you try and run that implicit cursor as a standalone query, you need to remove the INTO <var> bit - that's a pl/sql construct, not a sql one.

    Clive
  • 8. Re: create_assignment_extra_info API
    christm31 Newbie
    Currently Being Moderated
    Yes, I see what you mean.
    Have changed it to be the following which takes out the 'into' - however still not getting the message or any rows inserted...

    DECLARE
    v_assignment_id NUMBER;
    v_extra NUMBER;
    v_object NUMBER;
    v_api_error VARCHAR (500);
    v_errcode NUMBER;

    CURSOR c1
    IS
    select asg.assignment_id
    from per_all_people_f per, per_all_assignments_f asg, per_all_positions pos, per_position_definitions def, per_addresses adr, per_assignment_extra_info paei, xxsac_pension_ees ees
    where 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 sysdate between asg.effective_start_date and asg.effective_end_date
    and asg.position_id = pos.position_id
    and pos.date_end is null
    and pos.position_definition_id = def.position_definition_id
    and def.end_date_active is null
    and asg.assignment_id = paei.assignment_id
    and paei.information_type = 'XXSAC_PAYROLL_NOTIFICATION_DET'
    and per.employee_number = ees.employee_number
    and paei.aei_information20 = ees.job_no;

    BEGIN
    FOR rec IN c1
    LOOP
    BEGIN
    dbms_output.put_line('Test Message');
    hr_assignment_extra_info_api.create_assignment_extra_info
    (p_assignment_id => rec.assignment_id,
    p_information_type => 'XXSAC_INITIAL_ASSESSMENT',
    p_aei_information_category => 'XXSAC_INITIAL_ASSESSMENT',
    p_assignment_extra_info_id => v_extra,
    p_object_version_number => v_object,
    p_aei_information1 => '1',
    p_aei_information2 => sysdate );


    COMMIT;
    EXCEPTION
    WHEN OTHERS
    THEN
    v_api_error := SQLERRM;
    v_errcode := SQLCODE;
    END;
    END LOOP;
    END;
  • 9. Re: create_assignment_extra_info API
    clive_t Pro
    Currently Being Moderated
    OK, so when you run that query as a standalone query now, how many rows do you get?
  • 10. Re: create_assignment_extra_info API
    christm31 Newbie
    Currently Being Moderated
    It brings back 3,050 rows

    Martin
  • 11. Re: create_assignment_extra_info API
    clive_t Pro
    Currently Being Moderated
    Silly question, maybe, but: do you have DBMS_OUTPUT enabled in your TOAD session?

    One other thing to suggest would be to insert another put_line statement, in your exception, to display any resulting error messages returned from the api call.


    Clive
  • 12. Re: create_assignment_extra_info API
    GiuseppeBonavita Journeyer
    Currently Being Moderated
    instead of:

    p_aei_information2 => sysdate

    try the following:

    p_aei_information2 => fnd_date.date_to_canonical(sysdate)


    the parameter might be passed in the wrong format. p_aei_information2 is a varchar2, and to store the date value should be converted into a string with the correct format.

    hope this helps.

    Edited by: Giuseppe Bonavita on 06-Mar-2013 09:44
  • 13. Re: create_assignment_extra_info API
    christm31 Newbie
    Currently Being Moderated
    Thanks.
    Tried taking out the insert to aei_information2 completely but it's still not working.
    Not getting any message and not inserting any records either

    Martin
  • 14. Re: create_assignment_extra_info API
    Sanjay Singh Pro
    Currently Being Moderated
    Hi Martin,

    Try to first insert a records from front end and check for the values inserted in the table and try to insert the same values by directly calling the API with a hard coded values for a specific assignment_id into a block to debug the issue. If the API is inserting the data for hard coded values then there will some issue in the cursor data fetch and you can fetch the data accordingly.

    Inserting data in EIT should not be a challenge, try various options and you can definitely do it.
    Thanks,
    Sanjay
1 2 Previous Next

Legend

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