Search This Blog

Wednesday, December 4, 2013

AME Multiple Approvers fetch using Table Types http://documents.club-oracle.com/downloads.php?do=file&id=10545

Business Requirement –
As soon as employee puts leave, it should go to line manager for approval. After line manager it should go to HRBS (Human Resource Business Support). For one CC, there can be many HRBS people. HRBS data is stored in some other system. Data can be accessed directly from EBS database. Data is not stored in Transaction Roles.
Below solution considers using table type to fetch HRBS and fetch in AME.
Solution:




Approval Group:

Query of Approver Group:
SELECT 'person_id:'||person_id
from table(ekg_sshr_uae_ame_process_pkg.unpaid_leave_hrbs(:transactionId))


Create Rule:






Run Test Case 2:

Cheeersss….

Tuesday, October 1, 2013

Oracle HRMS PTO Accrual Plan Setup

Oracle HRMS PTO Accrual Plan Setup

Define and link an element for the plan's absence type.

a.       Set your effective date to a day on or before the start of the first payroll period for which you want to enter absences.

b.      If this is an absence element for a PTO accruals plan, do not select Process in Run. Uncheck this box if necessary.

c.       Save the element, then choose the Input Values button.


To link the absence element:

1. Set your effective date to a day on or before the start of the first payroll period for which you want to enter absences.

2. In the Element Link window, select the absence element.

3. Select eligibility criteria for this absence element. If you want to make the element available to all employees, do not select any criteria.

4. Save the link. Then define the absence type associated with this absence element. 


To define an absence type:

1.      Enter a name and category for the absence type.


 

2.      In the Associated Element region, select the element defined for this absence type. The name of the element's input value that holds the time taken or time remaining for this absence type appears in the Input Value field, and the unit of measure for the input value appears in the Units region.


 

3.      In the Balance region, select Increasing for each entry to the absence element's input value to add to a running total of time taken to date for this absence type. Select increasing balances for absence types for PTO accrual plans, and for most other absence types.


 

4.      Optionally, select reasons that are valid for entries of this type of absence.


 

5.      Save the absence type.


To define a PTO accrual plan:

1.      Enter the plan name, and select an accrual category of either Sick or Vacation for it.

2.      Select the start rule for the plan in the Accrual Start field. This rule determines the date on which a plan participant begins to accrue PTO.

3.      Select Days or Hours in the Accrual Units field. This selection must accord with the input value units selected for the element that records accrued time taken under this plan.

4.      In the Name field of the Absence Information region, select the element associated with the plan's absence type. The name of the element's input value that holds the entries or hours or days absent appears in the Units field.

5.      If the start rule for this plan is Hire Date or Beginning of Year you can enter a period of ineligibility, during which a plan participant can accrue PTO but cannot use accrued PTO. For example, enter 3 in the Length field and select Calendar Month in the next field.

Save your work, and then set up length of service bands for the plan. Choose the Accrual Bands button to go to the Accrual Bands window. 


See the setup for accrual bands. First line is applicable for 10 years with annual leave 30 days carrying 10 over to next year.


Net Calculation Rules is as follows:


Note: When Accrual plan is created along with that, three more elements would be created as follows:

  • the element that represents the plan
  • the element that holds unused, accrued time for carryover
  • the element that holds unused, accrued time that cannot be carried over.

    For the above elements, links are also created automatically. Please check these plans and make sure if these are similar to Absence element link. 


    Like the above, two more.


     

    To enroll participants in a PTO accrual plan:


     

    

     You can check the accrued leaves of the employee who joined on 06-Jun-2010 in Accruals window.


    As you change the effective dates, you would get the desired result. In the present company, accrued leaves per month are 2.5 days. According to standard formulae applied to the above accrual plan, 2.5 days can be accrued if the employee works for the complete month. (for modifying this functionality, you can change the code in fast formulae)

    So, as the employee joined on 6th June 2010, upto 31st Dec 2010, employee is entitled to get 15 days. 


    In the above screen you can see the carried over 10 days of leave for the next year even the accrued leaves by 31-Dec-2010 are 15. (This rule was defined in accrual bands window.)

    Note: To get this carried over leaves, you have to run PTO carry over process in HRMS super user.

    Below screenshot is for present period. So, you can view the accrued leaves period wise here. 


    For enrollment of an individual employee, perhaps as a part of the hiring process, enter the element representing the plan for him or her using the Element Entries window.

Tuesday, September 3, 2013

Retropay by Element Errors: APP-PAY-06881: Error ORA-01403: no data found has occurred in table PER_TIME_PERIODS

Problem Statement:
When attempting to run the RetroPay by Element process the following error occurs


ERROR
HR_6881_HRPROC_ORA_ERR
SQLERRMC ORA-01403: no data found
SQL_NO 3076
TABLE_NAME PER_TIME_PERIODS
APP-PAY-06881: Error ORA-01403: no data found has occurred in table PER_TIME_PERIODS at location 3076
Cause: an oracle error has occurred. The failure was reported on table
PER_TIME_PERIODS at location 3076 with the error text ORA-01403: no data found
Action: Please contact your support representative.

Solution:
To implement the solution, please execute the following steps:
1. Change the definition of the assignment set, choosing the correct payroll.
2. Submit the Retropay by Element process using the redefined assignment set.
3. If the issue is resolved, please migrate the solution as appropriate to other environments

Ref - 401208.1

Tuesday, July 30, 2013

How To Purge E-Mail Notifications From The Workflow Queue So The E-Mail Is Not Sent



1. Update the notifications you do not want sent, in the WF_NOTIFICATIONS table. Check the WF_NOTIFICATIONS table. Records where status = 'OPEN' and mail_status = 'MAIL' are notifications that will have an e-mail notification sent.

SQL> select notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS
where status = 'OPEN' and mail_status = 'MAIL';


This should show which notifications are waiting to be e-mailed.

One can use the BEGIN_DATE column to help narrow down the ones not to send if one only wants to stop the e-mails from a specific date range.

To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. The
mailer will think the e-mail has already been sent and it will not send it again.. (Users can
still reply to the notification from the worklist page in the applications).
Example:
update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';
OR
update wf_notifications
set mail_status = 'SENT'
where end_date is not null
and status = 'CLOSED'
and MAIL_STATUS = 'MAIL';


This will update all notifications waiting to be sent by the mailer.

2. Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent. It will then populate the queue with the current data in the wf_notifications table.
Since you have changed the mail_status = 'SENT" it will not enqueue these messages again.. Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.

sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr

Example Syntax:

sqlplus apps/apps@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps apps applsys


3. Now start the mailer.


Ref Metalink Note - Doc ID 372933.1

Sunday, July 28, 2013

How to Rollback SSHR Transaction in Oracle Self Service

How to Rollback SSHR Transaction in oracle apps.begin
hr_transaction_api.rollback_transaction(p_transaction_id);
end;

commit;

Note:

p_transaction_id is the transaction_id that you will find in hr_api_transaction table.

After executing the above statement you will not find any record in hr_api_transaction

OTL API to delete whole time card for the week

DECLARE
   -- Constant declarations
   l_otl_appl_id CONSTANT NUMBER (3) := 809; --OTL application id
   l_resp_appl_id NUMBER (10) := 51102; --OTL Responsibility id
l_user_id VARCHAR2 (20) := 120345; -- id from fnd_user table
BEGIN
-- First initialize your session, this needs to be done for internal reasons so
-- the TimeStore knows who is trying to deposit the information. When you log
-- into SS, the same is done for you by the framework, here however we have to do
-- it manually.
FND_GLOBAL.
APPS_INITIALIZE (user_id => l_user_id,
resp_id => l_resp_appl_id,
resp_appl_id => l_otl_appl_id); -- This is the appl_id for OTL, do not change

--
--Delte time card API calling
--
hxc_timestore_deposit.DELETE_TIMECARD (p_building_block_id => 3423423); --p_building_block_id is the timecard id with scope 'Timecard' and endate should be ''12/31/4712

COMMIT;
END;