This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Jul 3, 2013 12:16 AM by User523893-OC RSS

Absence Management

user8013866 Newbie
Currently Being Moderated

Dears,

 

I need to get the current Transaction_Id in BG_ABSENCE_DURATION Formula. how to achieve this.

R12.1.3

 

Thank you

  • 1. Re: Absence Management
    Adnan.Salim Newbie
    Currently Being Moderated

    Hi,


    You can use input value absence_attendance_id.


  • 2. Re: Absence Management
    user8013866 Newbie
    Currently Being Moderated

    Thank you Adnan,

     

    Actually i need to get the Transaction_id not Absence_Attendance_Id, as you know when you craete new Absence and before approved

      it saved on (HR_API_TRANSACTIONS) Table. i need The Transaction id.

     

    Thank you

  • 3. Re: Absence Management
    Sanjay Singh Pro
    Currently Being Moderated

    Hi,

     

    Can you explain your requirement in detail, why you are trying to fetch the transaction_id within BG_ABSENCE_DURATION?

     

    Thanks,
    Sanjay

  • 4. Re: Absence Management
    user8013866 Newbie
    Currently Being Moderated

    Dear Sanjay,

     

    Actually i'm trying to create Validation on (BG_ABSENCE_DURATION) Formula to prevent employees to take (Annual vacation days) more than the (Accrual balance),

    The validation should calculate the total of (Approved Vacations Days) + (Pending Vacations Days) + (Current Vacation/Transaction Days).

    keep in mind that the (BG_ABSENCE_DURATION) Formula called on each approver, so the status of the (Current Vacation/Transaction) will become (Pending Vacation) after submit the

    Transaction. so before submit the transaction the validation will calculate fine, but after submit and the status become (Pending) for the current transaction, the validation will calculate the

    (Current Transaction) Twice.

    So i need the transaction_id to exclude it from the query that retrieve the total of transaction days with pending approval.

     

    function get_a_p_SICK(p_assignment_id number, p_date_start date, p_date_end date, p_duration varchar2, p_absence_type NUMBER) return number as
    V_PENDING            NUMBER;
    V_APPROVED           NUMBER;
    l_start_date         DATE;
    l_end_date           DATE;
    l_accrual_end_date   DATE;
    l_accrual            NUMBER;
    l_net_entitlement    NUMBER;
    V_EXCEED             NUMBER;
    V_TOT                NUMBER;
    begin
    --
    --APPROVED VACATIONS
    --
    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 (18066,19061)
        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;
      --
      --PENDING VACATIONS
      --
      SELECT NVL(sum(NVL(hats.information8,0)),0) + NVL(sum(NVL(round(hats.information7/8,2),0)),0)
      INTO   V_PENDING
      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 (18066,19061)
       
        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);
       --
       --NET ACCRUAL
       --
        per_accrual_calc_functions.get_net_accrual(P_Assignment_ID         => P_ASSIGNMENT_ID
                                                  ,P_Plan_ID               => 3063
                                                  ,P_Payroll_ID            => 82
                                                  ,P_Business_Group_ID     => 83
                                                  ,P_Assignment_Action_ID  => -1
                                                  ,P_Calculation_Date      => P_DATE_START
                                                  ,P_Accrual_Start_Date    => null
                                                  ,P_Accrual_Latest_Balance=> null
                                                  ,P_Calling_Point         => 'FRM'
                                                  ,P_Start_Date            => L_START_DATE
                                                  ,P_End_Date              => L_END_DATE
                                                  ,P_Accrual_End_Date      => L_ACCRUAL_END_DATE
                                                  ,P_Accrual               => L_ACCRUAL
                                                  ,P_Net_Entitlement       => L_NET_ENTITLEMENT);
      
       IF L_ACCRUAL IS NOT NULL THEN
          IF p_absence_type = 18066 THEN /*Annual VACATION DAYS*/
            V_TOT := NVL(V_APPROVED,0) + NVL(V_PENDING,0) + NVL(P_DURATION,0);
          END IF;
          IF P_ABSENCE_TYPE = 19061 THEN/*Annual VACATION HOURS*/
             V_TOT := NVL(V_APPROVED,0) + NVL(V_PENDING,0) + NVL(round(P_DURATION/8,2),0);
          END IF;
          IF V_TOT > nvl(L_ACCRUAL,0) THEN
             V_EXCEED := 1;
          ELSE
             V_EXCEED := 0;  
          END IF;
       END IF;

    RETURN V_EXCEED;  
     
      EXCEPTION
       WHEN OTHERS THEN
        NULL;
      
    END;

     

     

    Please Advice

     

    Thank you

  • 5. Re: Absence Management
    Sanjay Singh Pro
    Currently Being Moderated

    Hi,

     

    The requirement mentioned by you of not allowing negative balances is a common requirement in all absence implementation.

     

    If you are on R12.1.2 and later you can use the profile option HR: Allow Absence Negative Balance - No and get the Patch 10036531 applied to achieve the requirement without any customization.

     

    See note ids -

    How can you use the new functionality of the system profile "HR Allow Absence Negative Balance" [ID 1457958.1]

    Unable To Apply For Leave When Balance Is Negative After Application Of Patch 10036531 [ID 1214653.1]

     

    For lower versions you will have to write a user hook to achieve your requirement as below:

    1. Create a procedure as below:

    PROCEDURE check_negative_balance(

        p_absence_attendance_type_id IN NUMBER ,

        p_exceeds_pto_entit_warning  IN BOOLEAN ,

        p_date_start                 IN DATE)

    AS

      l_abs_category VARCHAR2(20);

      l_absence_name VARCHAR2(100);

      CURSOR csr_get_abs_cat

      IS

        SELECT ABSENCE_CATEGORY,

          name

        FROM PER_ABSENCE_ATTENDANCE_TYPES

        WHERE absence_attendance_type_id = p_absence_attendance_type_id;

    BEGIN

      --Get Absence Category

      OPEN csr_get_abs_cat;

      FETCH csr_get_abs_cat INTO l_abs_category, l_absence_name;

      CLOSE csr_get_abs_cat;

      IF p_exceeds_pto_entit_warning = true THEN

        fnd_message.set_name('PER','PER_449875_ABS_NEGBAL');

        FND_MESSAGE.set_token('ABSTYPE', l_absence_name, FALSE);

        fnd_message.raise_error;

      END IF;

    END;

     

     

     

    3. Hook the above procedure to Hook package HR_PERSON_ABSENCE_BK1

    and hook procedure CREATE_PERSON_ABSENCE_A

     

    select * from HR_API_HOOKS where hook_package = 'HR_PERSON_ABSENCE_BK1' and hook_procedure = 'CREATE_PERSON_ABSENCE_A' and API_HOOK_TYPE = 'AP'

     

    For registration of user hook refer link - http://www.oracleport.com/understanding-and-using-api-user-hooks/

     

    try the POC and let me know if you need further details.

     

    Thanks,

    Sanjay

  • 6. Re: Absence Management
    User523893-OC Newbie
    Currently Being Moderated

    Thank you Sanjay,

     

    Actualy, i know that i can use user hook and the profile option, but suggested solutions not take Transactions with status (Pendging Approval) in considerations.

    Example:

      1- Employee (x) has 5 Days as an accrual plan, effective date on 20/06/2013.

     

      2- Employee (x) Submit (Annual Vacation) from 19/06/2013 to 19/06/2013 => 1 Day.

      3- The submited Vacation become Approved.

     

      4- Employee (x) Submit (Annual Vacation) from 21/06/2013 to 21/06/2013 => 1 Day.

      5- The Submited Vacation still on status (Pending Approval).

     

      6- On 21/06/2013 the Employee (x) Submit new Annual Vacation with 4 Days.

     

      7- So (1 Days) on Step#2 (Approved) + (4 Days) on step#6 + (1 Day) on step#4 (Pending Approval) = 6 Days > The Accual Balance(5 Days) Step#1.

      

    So, i need the validation to consider the (Pending Approval Vacation) + (Approved Vacation) + (new Vacation).

     

    Please advice

     

    Thank you

  • 7. Re: Absence Management
    User463450-OC Newbie
    Currently Being Moderated

    Hi,

     

    You have to write custom logic in your user hook package instead of using FF for "So, i need the validation to consider the (Pending Approval Vacation) + (Approved Vacation) + (new Vacation).".

     

    Follow the below process.

     

    For Approved Vacation -- Get the sum of applied leaves from per_absence_attedancies

    For Pending Details -  get the details from hr_api_transactions and hr_api_transaction_steps (Column name : Information will be having applied Leave days), here you need to pass your WF             process name and transaction_ref_table ='PER_ABSENCE_ATTENDANCES'    

    New Vacation : hope you know how to get this ..

     

    Let us know for any clarifications.

     

    Thx,

    Balaji

  • 8. Re: Absence Management
    User523893-OC Newbie
    Currently Being Moderated

    Thank you Balaji,

     

    Actually i did, but my case as i mentioned before that the new/Current Transaction will become (Pending Approval) after submit the transaction, so the current transaction

    will be calculated twice (one in Pending Approval Select), and the another is the parameter (P_Duration).

     

    So i have 2 levels:

    1- Before Submit the Vacation, (Approved Vacations from Per_Absence_attendances Table) + (Pending Vacations from Hr_api_Transactions, Hr_api_transaction_steps Tables) + (Current Transaction).

    2- After Submit the transaction, the (Current Transaction/Vacation) will retrieved from (Pending Vacations from Hr_api_Transactions, Hr_api_transaction_steps Tables), so

         when use this (Approved Vacations from Per_Absence_attendances Table) + (Pending Vacations from Hr_api_Transactions, Hr_api_transaction_steps Tables) + (Current Transaction) it will calculate

         the current transaction twice, so i need to exclude it from the (Pending Vacation Query).

     

    For this reason i need to get the current transaction_id to add new condition on (Pending Approval query), where hat.transaction_id <> :Current_Transaction_id

     

    Please read the discussion to see my queries.

     

    Thank you

  • 9. Re: Absence Management
    User463450-OC Newbie
    Currently Being Moderated

    Dear,

     

    can you tell us,where you are trying to put this validation. is it in Leave management --> Create absence page?

     

    If yes, then no need to validate the same in FF. Bcz, once your leave request is approved then only absence will be created. so based on your Absence setup element entry will happen.

     

    Thx,

    Balaji

  • 10. Re: Absence Management
    User523893-OC Newbie
    Currently Being Moderated

    Dear Balaji,

     

    Actualy i tryed the validation on Self service Page, so i tryed it on (User hook) and (BG_ABSENCE_DURATION Fast Formula).

    i just need to get the current Transaction Id either on the FF or on the User Hook.

     

    Thank you

     


  • 11. Re: Absence Management
    User523893-OC Newbie
    Currently Being Moderated

    Dear all,

     

    Basically my question is : how can i derive (Transaction id) for the same absence in the user hook. 

     

    Please advice.

     

    Thank you

  • 12. Re: Absence Management
    Sanjay Singh Pro
    Currently Being Moderated

    Hi,

     

    I think you are almost there as per mentioned in your reply:

    1. You have the approved absences

    2. You have the net accrual value

    3. You have a basic query for fetching the Pending Approval leaves (You need to exclude the current transaction as its record is also present in HR_API_TRANSACTIONS table)

     

    I think the only way to derive this will be that for the current absence details within the user hook you will have to derive the transaction_id which needs to be excluded by reading the absence data from XML data stored in TRANSACTION_DOCUMENT. Within the transaction document you have the details like Absence type, start date, end date, reason, comments etc which you need to fetch one by one for all pending approval transaction as per your above query in a cursor and within the cursor try to match the values with the current record for user hook

     

    For the record which match exclude the addition of days as it is the same transaction

     

    IF p_star_date = l_transaction_start_date and p_end_date = l_transaction_end_date and etc etc

    THEN

        NULL

    ELSE

       v_pending = v_pending + l_days;

    END IF;

     

    To know how to read XML data stored in TRANSACTION_DOCUMENT you can refer the - http://apps2fusion.com/apps-training/apps-functional-documents/75-ame-sshr/436-parse-xml-hrapitransactions-sshr-hrms-self-service

     

    A sample of it can be as below and transaction_id can be all the transactions which you are fetching in your pending approval query


       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;

     

    Try the POC and let me know if this solves your requirment.

     

    Thanks,

    Sanjay

  • 13. Re: Absence Management
    User523893-OC Newbie
    Currently Being Moderated

    Thank you so much Sanjay,

     

    I got this error when add the below where condition on my Select:

    an alias is not allowed here (reserved for XMLCOLATTVAL, XMLFOREST and XMLATTRIBUTES only)

     

    AND HAT.TRANSACTION_ID <> ( SELECT xx_api.TRANSACTION_ID

                                     FROM HR_API_TRANSACTIONS xx_api,

                                         TABLE(xmlsequence(extract(xmlparse(document transaction_document wellformed),

                                         '/Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow'))) xx_row

                                     WHERE extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/DateStart') = P_DATE_START

                                       AND extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/DateEnd')   = P_DATE_END

                                       AND extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/TimeStart') = P_START_TIME

                                       AND extractvalue(value(xx_row), '/PerAbsenceAttendancesEORow/TimeStart') = P_END_TIME);

     

    Thank you

  • 14. Re: Absence Management
    Sanjay Singh Pro
    Currently Being Moderated

    Hi,

     

    As mentioned earlier in my note dont try to merge both the queries. Based on your earlier query fetch all the transaction_is in a cursor

     

    and within the cursor loop write your logic

     

    CURSOR xx_get_pending_transactions

    is

    select transaction_id

    <your query>

     

    BEGIN

    FOR i in xx_get_pending_transactions

    LOOP

       select

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

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

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

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

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

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

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

    INTO l_abs_att_id, l_ab_type_id, l_date_start, l_ Proj_date_start, l_date_end, l_proj_date_end, l_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 = i.transaction_id;

     

    v_pending := 0;

        IF p_start_date = l_start_date etc

        THEN

              --- it means current transaction

            NULL; -- do nothing

        ELSE

             v_pending = v_pending + l_days   -- l_days also can be fetched from XML Data, check XML data for reference

        END IF;

    END;  

     

     

    Thanks,

    Sanjay

1 2 Previous Next