1 2 Previous Next 19 Replies Latest reply: Jul 3, 2013 2:16 AM by User523893-OC Go to original post RSS
      • 15. Re: Absence Management
        User523893-OC

        Hi

         

        I Got the Same Error,

         

        CURSOR C_GET_PENDING IS
        SELECT HATS.TRANSACTION_ID , DECODE(P_ABSENCE_TYPE, 19062, round(hats.information7/8,2), 18064, hats.information8) TOT
        FROM   hr_api_transactions      hat,
                hr_api_transaction_steps hats,
                per_all_people_f         per,
                per_all_assignments_f    paaf
        WHERE hat.transaction_id                                                    = hats.transaction_id
           and hat.CREATOR_PERSON_ID                                                 = per.person_id
           and hat.TRANSACTION_IDENTIFIER                                            = 'ABSENCES'
           and to_char(fnd_date.CANONICAL_TO_DATE(hats.information1),'YYYY')         = TO_CHAR(P_DATE_START,'YYYY')
           and to_char(fnd_date.CANONICAL_TO_DATE(hats.information1),'MM')           = TO_CHAR(P_DATE_START,'MM')
           AND per.person_id                                                         = paaf.person_id
           and paaf.assignment_id                                                    = p_assignment_id   
           AND hr_absutil_ss.getapprovalstatus(hat.transaction_id,NULL)              = ('Pending Approval')
           AND hats.information5                                                     in (19062, 18064)
           AND HATS.INFORMATION5                                                     = P_ABSENCE_TYPE
           AND paaf.EFFECTIVE_START_DATE                                             = (SELECT MAX(EFFECTIVE_START_DATE)
                                                                                        FROM   PER_ALL_ASSIGNMENTS_F I_PAAF
                                                                                        WHERE  I_PAAF.PERSON_ID = PAAF.PERSON_ID)
           AND per.EFFECTIVE_START_DATE                                              = (SELECT MAX(EFFECTIVE_START_DATE)
                                                                                        FROM   PER_ALL_PEOPLE_F I_PER
                                                                                        WHERE  I_PER.PERSON_ID = PER.PERSON_ID);
        begin
        IF P_ABSENCE_TYPE IN (19062, 18064) THEN
        --
        --APPROVED VACATIONS
        --
        BEGIN
          SELECT nvl(SUM(nvl(ABSENCE_DAYS,0)),0) + nvl(SUM(nvl(round(ABSENCE_hours/8,2),0)),0)
          INTO   V_APPROVED
          FROM   PER_ABSENCE_ATTENDANCES  paa
                ,per_all_assignments_f   paaf
          WHERE ABSENCE_ATTENDANCE_TYPE_ID IN (18064,19062)
            AND  paa.PERSON_ID              = paaf.PERSON_ID
            and  paaf.assignment_id         = P_ASSIGNMENT_ID
            AND  TO_CHAR(DATE_START,'YYYY') = TO_CHAR(P_DATE_START,'YYYY')
            AND  TO_CHAR(DATE_START,'MM')   = TO_CHAR(P_DATE_START,'MM')
            AND  paaf.effective_start_date  = (select max(i_paaf.effective_start_date) from per_all_assignments_f i_paaf where i_paaf.person_id = paaf.person_id)
            AND  PAAF.BUSINESS_GROUP_ID     = 83;
        END;
        ---------------------------------------------------------------------------------------------------------------------------------------------------------
        BEGIN
        FOR i in C_GET_PENDING LOOP
           select extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/DateStart'), 
                  extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/DateEnd')   ,
                  extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/TimeStart') ,
                  extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/TimeEnd')  ,
                  extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/PersonID')   

           INTO V_DATE_START,
                V_DATE_END,
                V_TIME_START,
                V_TIME_END,
                V_PERSON_ID
               
           FROM HR_API_TRANSACTIONS xx_api,
                TABLE(xmlsequence(extract(xmlparse(document transaction_document wellformed), '/Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow'))) xx_row
           where xx_api.transaction_id = i.transaction_id;
          
           v_pending := 0;

            IF p_DATE_START = V_DATE_START AND P_DATE_END = V_DATE_END AND P_START_TIME = V_TIME_START AND  P_END_TIME = V_TIME_END THEN
                NULL; -- do nothing
            ELSE
                 v_pending := v_pending + I.TOT;   -- l_days also can be fetched from XML Data, check XML data for reference
            END IF;
        END LOOP;
        END; 


        • 16. Re: Absence Management
          Sanjay Singh

          Hi,

           

          Can you try getting the second select also in a cursor as it is working at my place:

           

          CURSOR xx_get_pending_transactions

          is

          select transaction_id

          <your query>;

           

          CURSOR c_get_absence_details(c_transaction_id NUMBER)

            IS

             select

                extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/AbsenceAttendanceId') AS abs_att_id,

                extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/AbsenceAttendanceTypeId')  as ab_type_id,

                extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/DateStart')                AS date_start,

                extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/DateProjectedStart')       AS Proj_date_start,

                extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/DateEnd')                  AS date_end,

                extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/DateProjectedEnd')         as proj_date_end,

                extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/ObjectVersionNumber') as ovn

              FROM HR_API_TRANSACTIONS xx_api,

                TABLE(xmlsequence(extract(xmlparse(document transaction_document wellformed), '/Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow'))) xx_row

              where --xx_api.transaction_ref_id = 15791

                xx_api.transaction_id = c_transaction_id;

           

          rec_absence_details c_get_absence_details%rowtype;

          v_pending number;

           

          BEGIN
          FOR i in C_GET_PENDING LOOP

             OPEN c_get_absence_details(i.transaction_id);

              FETCH c_get_absence_details INTO rec_absence_details;

              close c_get_absence_details;

           

             v_pending := 0;

              IF p_DATE_START = rec_absence_details.DATE_START AND P_DATE_END = rec_absence_details.DATE_END THEN
                  NULL; -- do nothing
              ELSE
                   v_pending := v_pending + I.TOT;   -- l_days also can be fetched from XML Data, check XML data for reference
              END IF;
          END LOOP;
          END;

           

           

          Thanks,

          Sanjay

          • 17. Re: Absence Management
            User523893-OC

            Thank you so much Sanjay,

             

            It is fine, the error was becasue i generate the package on toad, it seems that the toad version not suppoted for this functionality, so i generate it on SQL Plus.

             

            Thank you so much.

             


            • 18. Re: Absence Management
              Sanjay Singh

              Hi,

               

              If your issue is sorted then mark the helpful posts and close this thread.

               

              Thanks,

              Sanjay

              • 19. Re: Absence Management
                User523893-OC

                Hi,

                 

                Actualy i dont have this option (mark answer as correct or helpful), in FAQ link i found that:

                 

                How can I mark answers as correct or helpful?

                This only works if you have flagged your thread as a question. You have 15 minutes after posting it to mark it as a question. After that, you will need to ask a moderator to do that for you.

                 

                Thank you

                1 2 Previous Next