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!!!!!

 

 

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;

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

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;

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';

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;

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….