Search This Blog

Wednesday, December 25, 2013

Running Oracle Fast Formula from PL/SQL Package Procedure

for c_formula_details_rec IN c_formula_details Loop

v_formula_id := c_formula_details_rec.formula_id;

end loop;


 

FF_EXEC.init_formula(p_formula_id => v_formula_id,

p_effective_date => v_eff_date,

p_inputs => l_f_inputs,

p_outputs => l_f_outputs);


 

IF l_f_inputs.count > 0 THEN


 

FOR i IN l_f_inputs.first .. l_f_inputs.last LOOP

IF l_f_inputs(i).name = 'BUSINESS_GROUP_ID' THEN

l_f_inputs(i).value := nBGId;


 

ELSIF l_f_inputs(i).name = 'ASSIGNMENT_ID' THEN

l_f_inputs(i).value := nAssignmentId;


 

ELSIF l_f_inputs(i).name = 'EFFECTIVE_DATE' THEN

l_f_inputs(i).value := v_eff_date;

ELSIF l_f_inputs(i).name = 'LV_ELEMENT_NAME' THEN

l_f_inputs(i).value := v_Row_Value;


 

END IF;

END LOOP;

END IF;


 

FF_EXEC.run_formula(p_inputs => l_f_inputs,

p_outputs => l_f_outputs,

p_use_dbi_cache => FALSE);


 

for i in l_f_outputs.first .. l_f_outputs.last loop

--

vActualValue:=lpad(l_f_outputs(i).value, 30);

end loop;

Dynamically Setting the Delivery Options from Back End

In Oracle Release 12.1.3 Oracle have come up with a new functionality to deliver the output of the Concurrent Program by various methods.


 


 

when we click on the Delivery options button it will open up another form which contains the information in which the output can be delivered.


 

1.IPP Printer : we can specify the Printer IP adress and user name and password for the printer and the no of copies to be printed(can be used if the required information is available).

2.Email: we can specify the email to whom the output has to be mailed after completion.(Specify any valid email address).

3.Fax : we can specify the fax details so that the the output can be faxed once the request is complted.

4.FTP: specify the FTP details to move the output to a particular folder in the server so that it can be access by the employees.


 

I have taken the Example Email which is generally commenly used.


 


 



 

By default the From field is filled with the email address of the logged in user and subject has the instance name and the report name and the logged in user name.


 

we can edit and change the subject and give as per the requirement.


 

In the TO field and the CC fields specify the Email address to whom the output has to be emailed.


 

If multiple emails are there then seperate them by using the ','.


 

This is the functionality used from the front end.


 

If we want to use the same thing from the back end which we generally used to submit the conucurrent program from the back end.


 

Use the below code.


 

/* Formatted on 2/20/2013 1:52:15 PM (QP5 v5.139.911.3011) */

DECLARE
l_conc_id NUMBER;
l_user_id NUMBER
:=
3651;
l_resp_id NUMBER
:=
20420;
l_resp_ppl_id NUMBER
:=
0;
l_boolean BOOLEAN;
BEGIN
l_boolean :=
fnd_request.add_delivery_option (TYPE
=>
'E',
-- this one to speciy the delivery option as Email
p_argument1 =>
'Testing the Email option from back end',
-- subject for the mail
p_argument2 =>
'abx@xyz.com',
-- from address
p_argument3 =>
'xyz@abc.com','adf@abc.com',
-- to address
p_argument4 =>
'',
-- cc address to be specified here.
nls_language
=>
'');
-- language option);
IF l_boolean =
TRUE
THEN
FND_GLOBAL.APPS_INITIALIZE (l_user_id, l_resp_id, l_resp_ppl_id);
-- intialize the apps.
l_conc_id :=
fnd_request.submit_request (application =>
'FND',program
=>
'FNDSCURS',
start_time =>
SYSDATE,sub_request =>
FALSE);
END
IF;
END;


 

Hope this document will be useful to every one as this one is the common requirement coming up now a days with all the clients.


 


 

Cheerss!!!!!


 


 

Thursday, December 19, 2013

Get Status of Concurrent Program Schedule


SELECT ftl.user_concurrent_program_name,
(SELECT user_name
FROM fnd_user
WHERE user_id = fcr.requested_by) submitted_by,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'CP_PHASE_CODE'
AND lookup_code = fcr.phase_code
AND view_application_id = 0) phase,
(SELECT LTRIM (meaning)
FROM fnd_lookup_values
WHERE lookup_type = 'CP_STATUS_CODE'
AND lookup_code = fcr.status_code
AND view_application_id = 0) status,
fcr.*
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_tl ftl
WHERE fcr.concurrent_program_id = ftl.concurrent_program_id
AND ftl.user_concurrent_program_name LIKE 'XX_SSHR_CREATE_NEW_USERS'
ORDER BY request_date DESC;

Tuesday, December 17, 2013

Update Custom WebADI Integrator

Situation:
I have created a Custom Integrator using Create Document Function from 'Desktop Integrator' Responsibility using 'HR Integrator Setup' Integrator.

I have Provided Pl/SQL Procedure as API Type. Integrator got created successfully but in the Document i am seeing headers like p_emp_num_i etc which are my Procedure's Input Parameter names.

I want to change these Column names to more user-friendly.

But now when I go to the Desktop Integration Manager->Manage Integrator the update icon is disabled for this custom integrator and also the source is set to Oracle.

Is there any way i can change the Prompt of the Column names?

Solution:

There are two ways for this situation.

1. Update the source into custom by following SQL:

UPDATE BNE_INTEGRATORS_B

SET SOURCE = 'C'

WHERE APPLICATION_ID = 'XXX'

AND INTEGRATOR_CODE = 'XXX';

*** Please make sure that this is a custom integrator.

2. Update the document header by BNE API.

API : BNE_INTEGRATOR_UTILS.UPDATE_INTERFACE_COLUMN_TEXT

Monday, December 16, 2013

Date Tracking in Oracle HRMS

To maintain employee data effectively Oracle HCM is using a mechanism called date tracking. The main motive behind the date track mode is to maintain past,present and future data effectively.
The various update date track modes are:

CORRECTION : Over writes the data. No history will maintain.
UPDATE : Keeps the history and new change will effect as of effective date
UPDATE_CHANGE_INSERT : Inserts the record and preserves the future
UPDATE_OVERRIDE : Inserts the record and overrides the future

Action: Created Employee # 22 on 01-JAN-2012

The record in PER_ALL_PEOPLE_F is as shown below.

Effective Start Date

Effective End Date

Employee Number

Marital Status

Object Version Number

01-JAN-2012

31-DEC-4712

24

 

2

Action: Updated record in CORRECTION mode

Effective Start Date

Effective End Date

Employee Number

Marital Status

Object Version Number

01-JAN-2012

31-DEC-4712

24

Single

3

Action: Updated record in UPDATE mode effective 01-JUN-2012 and Marital Status = Married

Effective Start Date

Effective End Date

Employee Number

Marital Status

Object Version Number

01-JAN-2012

31-MAY-2012

24

Single

4

01-JUN-2012

31-DEC-4712

24

Married

5

Action: Updated record in UPDATE mode effective 01-SEP-2012 and Marital Status = Divorced

Effective Start Date

Effective End Date

Employee Number

Marital Status

Object Version Number

01-JAN-2012

31-MAY-2012

24

Single

4

01-JUN-2012

31-AUG-2012

24

Married

6

01-SEP-2012

31-DEC-4712

24

Divorced

7

Action: Updated record in UPDATE_CHANGE_INSERT mode effective 01-MAR-2012 and Marital Status = Living Together

Effective Start Date

Effective End Date

Employee Number

Marital Status

Object Version Number

01-JAN-2012

29-FEB-2012

24

Single

8

01-MAR-2012

31-MAY-2012

24

Living Together

9

01-JUN-2012

31-AUG-2012

24

Married

6

01-SEP-2012

31-DEC-4712

24

Divorced

7

Action: Updated record in UPDATE_OVERRIDE mode effective 01-AUG-2012 and Marital Status = Divorced

Effective Start Date

Effective End Date

Employee Number

Marital Status

Object Version Number

01-JAN-2012

29-FEB-2012

24

Single

8

01-MAR-2012

31-MAY-2012

24

Living Together

9

01-JUN-2012

31-JUL-2012

24

Married

10

01-AUG-2012

31-DEC-4712

24

Divorced

11

 Delete Date Track Modes

The various delete date track modes are

ZAP : wipes all records
DELETE : Deletes  current record
FUTURE_CHANGE : Deletes current and future changes.
DELETE_NEXT_CHANGE : Deletes next change

Element Entry records are shown below.

Effective Start Date

Effective End Date

Element Entry Id

Object Version Number

01-JAN-2012

12-OCT-2012

129831

3

13-OCT-2012

19-OCT-2012

129831

5

20-OCT-2012

31-DEC-4712

129831

6

Action: Delete record in ZAP mode effective 14-JAN-2012

No rows

Action: Delete record in DELETE mode effective 14-OCT-2012

Effective Start Date

Effective End Date

Element Entry Id

Object Version Number

01-JAN-2012

12-OCT-2012

129831

3

13-OCT-2012

14-OCT-2012

129831

6

Action: Delete record in FUTURE_CHANGE mode effective 14-JAN-2012

Effective Start Date

Effective End Date

Element Entry Id

Object Version Number

01-JAN-2012

31-DEC-4712

129831

4

Action: Delete record in NEXT_CHANGE mode effective 14-JAN-2012

Effective Start Date

Effective End Date

Element Entry Id

Object Version Number

01-JAN-2012

19-OCT-2012

129831

4

20-OCT-2012

31-DEC-4712

129831

6

Function to Get Input Value ID from pay_element_entry_values_f



 


 

-- +=========================================================================================+

-- | Name : element_input_value_id

-- | Description : This function finds element input value ID

-- | Parameters : p_element_name, p_input_name, p_business_group_id ,p_assignment_id ,p_dated |

-- +==========================================================================================+


FUNCTION element_input_value_id (

p_element_name IN
VARCHAR2,

p_input_name IN
VARCHAR2,

p_business_group_id IN
NUMBER,

p_assignment_id IN
NUMBER,

p_dated IN
DATE


)


RETURN
NUMBER


IS


CURSOR cur_element_input_value


IS


SELECT pev.input_value_id


FROM pay_element_entries_f pef,

pay_element_entry_values_f pev,

pay_element_links_f pel,

pay_element_types_f pet,

pay_input_values_f piv

WHERE pet.element_name = p_element_name


AND piv.NAME
= p_input_name


AND pet.business_group_id = p_business_group_id


AND pef.assignment_id = p_assignment_id


AND pet.business_group_id = pel.business_group_id


AND pet.business_group_id = piv.business_group_id


AND pef.element_entry_id = pev.element_entry_id


AND pef.element_link_id = pel.element_link_id


AND pel.element_type_id = pet.element_type_id


AND pet.element_type_id = piv.element_type_id


AND pev.input_value_id = piv.input_value_id


AND p_dated BETWEEN pet.effective_start_date


AND pet.effective_end_date


AND p_dated BETWEEN pel.effective_start_date


AND pel.effective_end_date


AND p_dated BETWEEN pef.effective_start_date


AND pef.effective_end_date


AND p_dated BETWEEN pev.effective_start_date


AND pev.effective_end_date


AND p_dated BETWEEN piv.effective_start_date


AND piv.effective_end_date;



ln_input_value_id pay_input_values_f.input_value_id%TYPE;


BEGIN


OPEN cur_element_input_value;




FETCH cur_element_input_value


INTO ln_input_value_id;




CLOSE cur_element_input_value;




RETURN ln_input_value_id;


EXCEPTION


WHEN
OTHERS


THEN


RETURN
NULL;


END;

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.