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

    Absence Management




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



      Thank you

        • 1. Re: Absence Management


          You can use input value absence_attendance_id.

          • 2. Re: Absence Management

            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



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



              • 4. Re: Absence Management

                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;
                --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,'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;
                         V_EXCEED := 0;  
                      END IF;
                   END IF;

                RETURN V_EXCEED;  
                   WHEN OTHERS THEN



                Please Advice


                Thank you

                • 5. Re: Absence Management
                  Sanjay Singh



                  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)


                    l_abs_category VARCHAR2(20);

                    l_absence_name VARCHAR2(100);

                    CURSOR csr_get_abs_cat


                      SELECT ABSENCE_CATEGORY,



                      WHERE absence_attendance_type_id = p_absence_attendance_type_id;


                    --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_token('ABSTYPE', l_absence_name, FALSE);


                    END IF;





                  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.




                  • 6. Re: Absence Management

                    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.


                      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



                      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.




                      • 8. Re: Absence Management

                        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



                          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.




                          • 10. Re: Absence Management

                            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

                              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



                                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




                                   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


                                      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.




                                • 13. Re: Absence Management

                                  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



                                    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


                                    select transaction_id

                                    <your query>



                                    FOR i in xx_get_pending_transactions



                                          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


                                              --- it means current transaction

                                            NULL; -- do nothing


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

                                        END IF;






                                    1 2 Previous Next