Search This Blog

Sunday, December 15, 2013

Query to get User Values from UDTs


Query to get user values from UDTs

 

SELECT pur.row_low_range_or_name, pucif.value
FROM pay_user_column_instances_f pucif
,pay_user_rows_f pur
,pay_user_columns puc
,pay_user_tables put
WHERE put.user_table_id = puc.user_table_id
AND put.user_table_id = pur.user_table_id
AND pucif.user_row_id = pur.user_row_id
AND pucif.user_column_id = puc.user_column_id
AND trunc(SYSDATE) BETWEEN pur.effective_start_date
AND pur.effective_end_date
AND trunc(SYSDATE) BETWEEN pucif.effective_start_date
AND pucif.effective_end_date
AND puc.user_column_name = 'XXXXXX'
AND put.user_table_name = 'XX_APPLICATION_EXCEPTIONS';

Tuesday, December 10, 2013

Oracle HRMS API – Create Employee Element Entry


API - pay_element_entry_api.create_element_entry
Example --

Lets Try to Create Element Entry "Bonus" for Employee

 
 

DECLARE    ln_element_link_id                  PAY_ELEMENT_LINKS_F.ELEMENT_LINK_ID%TYPE;
   ld_effective_start_date            DATE;
   ld_effective_end_date             DATE;
   ln_element_entry_id                PAY_ELEMENT_ENTRIES_F.ELEMENT_ENTRY_ID%TYPE;
   ln_object_version_number     PAY_ELEMENT_ENTRIES_F.OBJECT_VERSION_NUMBER %TYPE;
   lb_create_warning                    BOOLEAN;
   ln_input_value_id                    PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE;
   ln_screen_entry_value            PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%TYPE;
   ln_element_type_id                  PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;


BEGIN

        -- Get Element Link Id
        -- ------------------------------
          ln_element_link_id :=      hr_entry_api.get_link
                                                          (       p_assignment_id      => 33561,
                                                                  p_element_type_id   => 50417,
                                                                  p_session_date          => TO_DATE('23-JUN-2011')
                                                          );
 
      
dbms_output.put_line( '  API: Element Link Id: ' || ln_element_link_id );

 
       -- Create Element Entry
       -- ------------------------------
       
pay_element_entry_api.create_element_entry
         (     -- Input data elements
               -- -----------------------------
               p_effective_date                     => TO_DATE('22-JUN-2011'),
               p_business_group_id          => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
               p_assignment_id                   => 33561,
               p_element_link_id                => ln_element_link_id,
               p_entry_type                           => 'E',
               p_input_value_id1               => 53726,
               p_entry_value1                      => 2500,
               -- Output data elements
               -- --------------------------------
               p_effective_start_date          => ld_effective_start_date,
               p_effective_end_date           => ld_effective_end_date,
               p_element_entry_id             => ln_element_entry_id,
               p_object_version_number  => ln_object_version_number,
               p_create_warning                 => lb_create_warning
         );
 
    
dbms_output.put_line( '  API: pay_element_entry_api.create_element_entry successfull - Element Entry Id: ' || ln_element_entry_id );  
 COMMIT;



EXCEPTION           WHEN OTHERS THEN
                            ROLLBACK;
                            dbms_output.put_line(SQLERRM);
END;

/


SHOW ERR;

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