1 2 Previous Next 28 Replies Latest reply: Mar 18, 2013 9:04 AM by christm31 RSS

    create_assignment_extra_info API

    christm31
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            It brings back 3,050 rows

                            Martin
                            • 11. Re: create_assignment_extra_info API
                              clive_t
                              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
                                Giuseppe Bonavita
                                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
                                  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
                                    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