Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Functional Documents
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new

Search Courses

Business Requirement:

Business wants to ensure that whenever an employee tries to overbook leaves the system should not allow him to do so. In case of exceptional situations the user will contact the Local HR who will then enter his/her leaves in the system.

In 11i Oracle Absence Management user/employee only gets a warning message stating that after you apply this leave your balance will be negative , but allows the user to ignore and proceed. The approver too does not gets any such message so the approver is completely in dark and so he unknowingly approves it.

In order to achieve this there are two options :

a) Upgrade your Application to R12

b) Write custom logic within user hook.

Now, it is not always possible to move to a higher version just for achieving some functionality although that would be best considering the added features you get with next upgrade, but as a stop gap solution we would try to see how we can get this done via user hook.

On investigation it was found that there is a particular out parameter p_exceeds_pto_entit_warning which gives the message on self-service screen. Since it is a out parameter we have to choose the API Hook of process type 'BP' and after insert.

Now I have written a package which takes p_exceeds_pto_entit_warning as input and displays the error message. I have also used a custom profile option XXC_LOA_RESTRICT_OVERBOOKING to ensure I have the option of switching on/off the functionality.

-- Profile Option Details—

Hook Imp 1

Name : XXC_LOA_RESTRICT_OVERBOOKING

User Profile Name : XXC: LOA Restrict Overbooking

Hierarchy Type Access Level : Responsibility and user ( visible and updatable)

User Access : Visible

SQL Validation: SQL="select meaning \"Yes or No\", lookup_code into :visible_option_value, :profile_option_value from fnd_lookups where lookup_type = 'YES_NO'" COLUMN="\"Yes or No\"(*)"

N.B: We have define this profile option value to ensure that the error will be thrown only when employees try to overbook using Self Service Responsibility but when the changes are to be incorporated from Absence Details screen it would allow HR to add details ( with seeded warning message).

In case you want to switch on this functionality just set the value of profile at Responsibility level to ‘Yes’ to switch off set the same to ‘No’.

This gives the flexibility that from Employee Self Service error will be displayed but not from core Absence Details form

---------- Create a Custom Message ----------------------------------------------

Message Name : XXC_HR_LOA_EMP_NOT_ENTITLED

Message : You have exceeded the maximum limit for this absence type. Please resubmit based on the local policy for this leave type.

Hook Imp 2

-------- Custom Package ----------------------------------------------------------------------------------------------------

Hook Imp 3          Hook Imp 4

-- Query to find API HOOK ID and MODULE ID

select hah.api_hook_id,ham.api_module_id

from apps.hr_api_hooks hah,

     apps.hr_api_modules ham

where hah.api_module_id = ham.api_module_id

and hah.hook_package = 'HR_PERSON_ABSENCE_BK1'

and hah.hook_procedure = 'CREATE_PERSON_ABSENCE_A'

-- api_hook_id = 3840, api_module_id = 1731

-- Register User HOOK by CALLING hr_api_hook_call_api.create_api_hook_call

DECLARE

P_VALIDATE BOOLEAN;

P_EFFECTIVE_DATE DATE;

P_API_HOOK_ID NUMBER;

P_API_HOOK_CALL_TYPE VARCHAR2(200);

P_SEQUENCE NUMBER;

P_ENABLED_FLAG VARCHAR2(200);

P_CALL_PACKAGE VARCHAR2(200);

P_CALL_PROCEDURE VARCHAR2(200);

P_API_HOOK_CALL_ID NUMBER;

P_OBJECT_VERSION_NUMBER NUMBER;

BEGIN

P_VALIDATE := TRUE;

P_EFFECTIVE_DATE := 01-JAN-1951;

P_API_HOOK_ID := 3840;-- derived from SQL1

P_API_HOOK_CALL_TYPE := 'PP';

P_SEQUENCE := 3000; -- any value greater than 2000, 1-2000 reserved for Oracle Use

P_ENABLED_FLAG := 'Y';

P_CALL_PACKAGE := 'XXC_HR_USER_HOOK_PKG'; -- custom package

P_CALL_PROCEDURE := 'XXC_LOA_RESTRICT_OVERBOOKING'; -- custom procedure

P_API_HOOK_CALL_ID := NULL; P_OBJECT_VERSION_NUMBER := NULL;

APPS.HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL ( P_VALIDATE, P_EFFECTIVE_DATE, P_API_HOOK_ID, P_API_HOOK_CALL_TYPE, P_SEQUENCE, P_ENABLED_FLAG, P_CALL_PACKAGE, P_CALL_PROCEDURE, P_API_HOOK_CALL_ID, P_OBJECT_VERSION_NUMBER );

     COMMIT;

dbms_output.put_line('API Hook ID: '|| P_API_HOOK_ID);

dbms_output.put_line('API OVN: '|| P_OBJECT_VERSION_NUMBER);

EXCEPTION

WHEN OTHERS THEN

               dbms_output.put_line('Error: '|| SQLCODE||SQLERRM);

END;

-- Output of above query

-- API Hook Call ID: 864

-- API OVN: 1

------Query to execute Pre-processor---------------------------------

Login to your unix enviornment and connect sqlplus

go to $PER_TOP/admin/sql execute hrahkone.sql

it will ask for module id derived from sql1

After completing the desired steps and setting the profile value of your Responsibility to ‘Yes’ /’ No’ the application will work as per business requirement.

-------------    pkgbody.Pkb-------------------------------

CREATE OR REPLACE PACKAGE BODY send_email
IS

/* ******************************************************************************************
* Package Name: send_email *
* Purpose: Send Email *
* *
* Procedures: *
* ----------- *
* send_email Invoke "XXC_SEND_EMAIL" Program *
* *
* Functions: *
* ---------- *
* get_subscribers: Get list of subscribers *
* *
* Change History: *
* --------------- *
* *
* Version Date Author Description *
* ------- ----------- ------------ ----------------------------------------------- *
* 1.0 02-Jul-2011 Ashish Send Email *
******************************************************************************************** */

/***********************************************************************************************
* Procedure: send_email *
* Description: Procedure to notify program run history *
* *
* Parameters: *
* ----------- *
* Name Description *
* ---------------------- ------------------------------------------------------------- *
* pn_request_id Request ID *
* pn_out_request_id Out Request ID *
* pc_subscribers Subscribers *
* pc_mail_subject Mail Subject *
* *
* Change History: *
* --------------- *
* *
* Version Date Author Description *
* ------- ----------- ------------ ------------------------------------- *
* 1.0 26-DEC-2011 Ashish Harbhajanka Initial Version *
************************************************************************************************/
PROCEDURE send_email
( pn_request_id IN NUMBER DEFAULT NULL,
pc_subscribers IN VARCHAR2,
pc_mail_subject IN VARCHAR2,
pb_sub_request IN BOOLEAN DEFAULT FALSE
)
IS

lc_proc_name VARCHAR2(100) := 'send_email.send_email';
lc_location VARCHAR2(50) := 'FLOW TRACE-1.100';
lc_errbuf VARCHAR2(2000) := NULL;
--lt_tokens error_handler.token_tbl_type DEFAULT error_handler.g_miss_token_tbl;

ln_request_id NUMBER := 0;

BEGIN
send_email.debug ( 'Start of ' || lc_proc_name || ' @ ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
send_email.debug ( ' ');

-- Print input parameters
-- ----------------------
send_email.debug ('Input Parameters: ');
send_email.debug ('----------------- ');
send_email.debug (' Request ID: ' || pn_request_id);
send_email.debug (' Subscribers: ' || pc_subscribers);
send_email.debug (' Mail Subject: ' || pc_mail_subject);
send_email.debug (' ');

-- Submitting Mailing Program
-- --------------------------
send_email.debug ( ' Submitting mailer program: ' || send_email.gc_notify_prog_name );
lc_location := 'FLOW TRACE-1.110';

ln_request_id := APPS.FND_REQUEST.SUBMIT_REQUEST
( application => send_email.gc_appl_name,
program => send_email.gc_notify_prog_code,
sub_request => pb_sub_request,
argument1 => pn_request_id,
argument2 => 'OUT',
argument3 => pc_subscribers,
argument4 => pc_mail_subject
);
COMMIT;

IF ( ln_request_id = 0 )
THEN
-- Get Error Message
-- -----------------
lc_errbuf := SQLCODE || SQLERRM;

send_email.debug (lc_errbuf);

RAISE_APPLICATION_ERROR(-20003, lc_errbuf);
END IF;

send_email.debug ( ' Successfully submitted: ' || send_email.gc_notify_prog_name || ' - request id: ' || ln_request_id );

send_email.debug ( 'End of ' || lc_proc_name || ' @ ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') );
EXCEPTION
WHEN OTHERS THEN
-- Get Error Message
-- -----------------
lc_errbuf := SQLCODE || SQLERRM;

RAISE_APPLICATION_ERROR(-20003, lc_errbuf);
END send_email;

/* ***************************************************************************************
* Function: get_subscribers *
* Description: Get list of subscribers *
* *
* Parameters: *
* ----------- *
* Name Description *
* ------------- ----------------------------------------------------------- *
* pc_all_errors All error flag *
* pc_by_error_category Error category (amc_error_log.error_category) *
* pc_by_source_stream Source stream (amc_error_log.source_stream) *
* pc_by_source_object Source Object (amc_error_log.source_object) *
* pc_by_source_identifier1 Source Identifier1 - Source system Name *
* pc_by_source_identifier2 Source Identifier2 - Source system Type *
* pc_by_source_identifier3 Source Identifier3 - Any other identifier *
* *
* Change History: *
* --------------- *
* *
* Version Date Author Description *
* ------- ----------- ------------ -------------------------------------- *
* 1.0 26-DEC-2011 Ashish Harbhajanka Initial Version *
**************************************************************************************** */
FUNCTION get_subscribers(pc_source_object IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS

CURSOR subscribers_cur
IS
SELECT flvv.tag
FROM apps.fnd_lookup_values_vl flvv
WHERE flvv.lookup_type = gc_subscribers_lookup
AND flvv.description = pc_source_object
AND flvv.enabled_flag = gc_yes
AND SYSDATE BETWEEN flvv.start_date_active AND NVL(flvv.end_date_active,SYSDATE);

lc_proc_name CONSTANT VARCHAR2(70) := 'send_email.get_subscribers';
lc_subscribers VARCHAR2(4000) := NULL;

BEGIN
send_email.debug( 'Start of ' || lc_proc_name || ' @ ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') );
send_email.debug( ' ' );

-- Print input parameters
-- ----------------------
send_email.debug( 'Input Parameters: ' );
send_email.debug( '----------------- ' );
send_email.debug( ' By Source Object: ' || pc_source_object );

-- Concatinate all subscribers into a string
-- -----------------------------------------
FOR subscribers_rec IN subscribers_cur
LOOP
lc_subscribers := lc_subscribers || subscribers_rec.tag || '; ';
END LOOP;

send_email.debug(' Subscribers: ' || lc_subscribers);

send_email.debug( 'End of ' || lc_proc_name || ' @ ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') );
send_email.debug( ' ' );

RETURN (lc_subscribers);

EXCEPTION
WHEN OTHERS THEN
RAISE;

END get_subscribers;

END send_email;
/

 

-------------------------------- pkgspec.pks  ------------------------------------

CREATE OR REPLACE PACKAGE send_email AUTHID CURRENT_USER
IS

/* ******************************************************************************************
* Package Name: send_email *
* Purpose: Send Email *
* *
* Procedures: *
* ----------- *
* send_email Invoke "XXC_SEND_EMAIL" Program *
* *
* Functions: *
* ---------- *
* get_subscribers: Get list of subscribers *
* *
* Change History: *
* --------------- *
* *
* Version Date Author Description *
* ------- ----------- ------------ ----------------------------------------------- *
* 1.0 02-Jul-2011 Ashish Send Email *
******************************************************************************************** */

gc_notify_prog_code CONSTANT VARCHAR2(30) := 'XXC_SEND_EMAIL';
gc_subscribers_lookup CONSTANT VARCHAR2(30) := 'XXC_GET_SUBSCRIBERS';


/*****************************************************************************************
* Procedure: send_email *
* Description: Procedure to notify program run history *
* *
* Parameters: *
* ----------- *
* Name Description *
* ---------------------- -------------------------------------------------------------*
* pn_request_id Request ID *
* pn_out_request_id Out Request ID *
* pc_subscribers Subscribers *
* pc_mail_subject Mail Subject *
* pb_sub_request Sub Request *
* *
* Change History: *
* --------------- *
* *
* Version Date Author Description *
* ------- ----------- ------------------ --------------------------------- *
* 1.0 26-DEC-2011 Ashish Harbhajanka Draft Version *
**************************************************************************************** */
PROCEDURE send_email
( pn_request_id IN NUMBER DEFAULT NULL,
pc_subscribers IN VARCHAR2,
pc_mail_subject IN VARCHAR2,
pb_sub_request IN BOOLEAN DEFAULT FALSE
);


/* ***************************************************************************************
* Function: get_subscribers *
* Description: Get list of subscribers *
* *
* Parameters: *
* ----------- *
* Name Description *
* ------------- ----------------------------------------------------------- *
* pc_all_errors All error flag *
* pc_by_error_category Error category (amc_error_log.error_category) *
* pc_by_source_stream Source stream (amc_error_log.source_stream) *
* pc_by_source_object Source Object (amc_error_log.source_object) *
* pc_by_source_identifier1 Source Identifier1 - Source system Name *
* pc_by_source_identifier2 Source Identifier2 - Source system Type *
* pc_by_source_identifier3 Source Identifier3 - Any other identifier *
* *
* Change History: *
* --------------- *
* *
* Version Date Author Description *
* ------- ----------- ------------------ --------------------------------- *
* 1.0 26-DEC-2011 Ashish Harbhajanka Draft Version *
**************************************************************************************** */
FUNCTION get_subscribers(pc_source_object IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;

END send_email;
/


Ashish Harbhajanka

About the Author

Ashish Harbhajanka

 

Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

l) UPK

........

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner