User hook on Special Info to attach element to Assignment entries screen:
1. Requirement once the employee applies for Allowance and get final approved , Once data entered into HR tables, then the allowance should be attached auto to Assignment Entries screen.
2. Solution using user hooks on SIT.
Step1: Creating Procedure for Creating Element Entries.
Step2: Creating Package for user hook.
Step3: Registering user hook.
Step1:
--FOR USER HOOK IN SIT Allowances
PROCEDURE CREATE_ELEMENT_ENTRY_UH_P
(P_BUSINESS_GROUP_ID IN NUMBER,
P_PERSON_ID IN NUMBER,
P_ANALYSIS_CRITERIA_ID IN NUMBER,
P_SEGMENT1 VARCHAR2, --ELEMENT_TYPE_ID
P_SEGMENT2 VARCHAR2, --EFFECTIVE DATE
P_SEGMENT11 VARCHAR2 --AMOUNT
--P_SEGMENT1 VARCHAR2
)
IS
/*DECLARE
P_BUSINESS_GROUP_ID NUMBER := 81;
--EVENT_TYPE VARCHAR2(20) := 'insert';
P_PERSON_ID NUMBER := 26876;
--P_ABSENCE_ATTENDANCE_ID NUMBER := 3033909;
P_SEGMENT1 VARCHAR2(20) := 589; --ELEMENT_TYPE_ID
P_SEGMENT2 VARCHAR2(20) := '2015/12/10 00:00:00';--EFFECTIVE DATE
P_SEGMENT11 VARCHAR2(20) := 100; --AMOUNT
*/
l_effective_start_date DATE := NULL;
l_effective_end_date DATE := NULL;
l_object_version_number NUMBER := NULL;
l_element_entry_id NUMBER := NULL;
l_create_warning BOOLEAN;
l_err_msg VARCHAR2 (500) := NULL;
L_ASS_ID NUMBER;
L_PAYROLL_ID NUMBER;
L_ELEMENT_LINK_ID NUMBER;
l_date_start DATE;
l_date_end DATE;
L_ABSENCE_ATTENDANCE_TYPE_ID NUMBER;
L_INPUT_VALUE_ID NUMBER;
l_reason NUMBER;
p_value6 VARCHAR2(1);
p_value7 VARCHAR2(1);
reqid number;
BEGIN
-----------TO GET ASSIGNMENT_ID, PAYROLL_ID
BEGIN
SELECT DISTINCT ASSIGNMENT_ID, PAYROLL_ID
INTO L_ASS_ID, L_PAYROLL_ID
FROM PER_ALL_ASSIGNMENTS_F X
WHERE X.PERSON_ID= P_PERSON_ID
AND X.PRIMARY_FLAG='Y'
AND TO_DATE(SUBSTR(P_SEGMENT2, 1, 10), 'YYYY/MM/DD') BETWEEN X.EFFECTIVE_START_DATE AND X.EFFECTIVE_END_DATE
;
END;
--gettting the Element_link_id of the person
--GET ELEMENT LINK
BEGIN
SELECT DISTINCT F.ELEMENT_LINK_ID,PIV.INPUT_VALUE_ID
INTO L_ELEMENT_LINK_ID, L_INPUT_VALUE_ID
FROM
PAY_ELEMENT_LINKS_F F,
PAY_ELEMENT_TYPES_F P,
PAY_INPUT_VALUES_F PIV
WHERE
TRUNC(SYSDATE) BETWEEN F.EFFECTIVE_START_DATE AND F.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
AND F.ELEMENT_TYPE_ID=P.ELEMENT_TYPE_ID
AND F.PAYROLL_ID = L_PAYROLL_ID
AND P.ELEMENT_TYPE_ID = P_SEGMENT1
AND F.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PIV.NAME = 'Override Amount';
END;
BEGIN
pay_element_entry_api.create_element_entry (
p_validate => FALSE,
p_effective_date => trunc(TO_DATE(SUBSTR(P_SEGMENT2, 1, 10), 'YYYY/MM/DD'),'mm'),--P_SEGMENT2
p_business_group_id => P_BUSINESS_GROUP_ID,
p_assignment_id => L_ASS_ID,
p_element_link_id => L_ELEMENT_LINK_ID,
p_entry_type => 'E',
p_input_value_id2 => L_INPUT_VALUE_ID, ---Override Amount
p_entry_value2 => to_number(P_SEGMENT11),--Amount
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_element_entry_id => l_element_entry_id,
p_object_version_number => l_object_version_number,
p_create_warning => l_create_warning
);
COMMIT;
DBMS_OUTPUT.put_line (
'Element has been uploaded: ' || l_element_entry_id
);
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);
END;
Step2:
CREATE OR REPLACE PACKAGE APPS.XXFUJ_CREATE_SIT_A_UHPKG IS
--USER HOOK PACKAGE FOR HR_SIT_BK1.CREATE_SIT_A
PROCEDURE XXFUJ_CREATE_SIT_LEV_VAL
--THIS WILL VALIDATE THE SIT WITH REFERENCE TO THE LEAVE EXISTS.
/*
Example: If a emp is on Annual leave from 01-jan-2017 to 30-jan-2017
now we are entering in SIT (Training) 10-jan-2017 to 15-jan-2017
As training days falls between the annual leave days, then the user entering the SIT will be notified by warning message.
*/
(P_PERSON_ID in NUMBER
,P_BUSINESS_GROUP_ID in NUMBER
,P_ID_FLEX_NUM in NUMBER
,P_EFFECTIVE_DATE in DATE
,P_COMMENTS in VARCHAR2
,P_DATE_FROM in DATE
,P_DATE_TO in DATE
,P_REQUEST_ID in NUMBER
,P_PROGRAM_APPLICATION_ID in NUMBER
,P_PROGRAM_ID in NUMBER
,P_PROGRAM_UPDATE_DATE in DATE
,P_ATTRIBUTE_CATEGORY in VARCHAR2
,P_ATTRIBUTE1 in VARCHAR2
,P_ATTRIBUTE2 in VARCHAR2
,P_ATTRIBUTE3 in VARCHAR2
,P_ATTRIBUTE4 in VARCHAR2
,P_ATTRIBUTE5 in VARCHAR2
,P_ATTRIBUTE6 in VARCHAR2
,P_ATTRIBUTE7 in VARCHAR2
,P_ATTRIBUTE8 in VARCHAR2
,P_ATTRIBUTE9 in VARCHAR2
,P_ATTRIBUTE10 in VARCHAR2
,P_ATTRIBUTE11 in VARCHAR2
,P_ATTRIBUTE12 in VARCHAR2
,P_ATTRIBUTE13 in VARCHAR2
,P_ATTRIBUTE14 in VARCHAR2
,P_ATTRIBUTE15 in VARCHAR2
,P_ATTRIBUTE16 in VARCHAR2
,P_ATTRIBUTE17 in VARCHAR2
,P_ATTRIBUTE18 in VARCHAR2
,P_ATTRIBUTE19 in VARCHAR2
,P_ATTRIBUTE20 in VARCHAR2
,P_SEGMENT1 in VARCHAR2
,P_SEGMENT2 in VARCHAR2
,P_SEGMENT3 in VARCHAR2
,P_SEGMENT4 in VARCHAR2
,P_SEGMENT5 in VARCHAR2
,P_SEGMENT6 in VARCHAR2
,P_SEGMENT7 in VARCHAR2
,P_SEGMENT8 in VARCHAR2
,P_SEGMENT9 in VARCHAR2
,P_SEGMENT10 in VARCHAR2
,P_SEGMENT11 in VARCHAR2
,P_SEGMENT12 in VARCHAR2
,P_SEGMENT13 in VARCHAR2
,P_SEGMENT14 in VARCHAR2
,P_SEGMENT15 in VARCHAR2
,P_SEGMENT16 in VARCHAR2
,P_SEGMENT17 in VARCHAR2
,P_SEGMENT18 in VARCHAR2
,P_SEGMENT19 in VARCHAR2
,P_SEGMENT20 in VARCHAR2
,P_SEGMENT21 in VARCHAR2
,P_SEGMENT22 in VARCHAR2
,P_SEGMENT23 in VARCHAR2
,P_SEGMENT24 in VARCHAR2
,P_SEGMENT25 in VARCHAR2
,P_SEGMENT26 in VARCHAR2
,P_SEGMENT27 in VARCHAR2
,P_SEGMENT28 in VARCHAR2
,P_SEGMENT29 in VARCHAR2
,P_SEGMENT30 in VARCHAR2
,P_CONCAT_SEGMENTS in VARCHAR2
,P_ANALYSIS_CRITERIA_ID in NUMBER
,P_PERSON_ANALYSIS_ID in NUMBER
,P_PEA_OBJECT_VERSION_NUMBER in NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY XXFUJ_CREATE_SIT_A_UHPKG IS
--USER HOOK PACKAGE FOR HR_SIT_BK1.CREATE_SIT_A
PROCEDURE XXFUJ_CREATE_SIT_LEV_VAL
--THIS WILL VALIDATE THE SIT WITH REFERENCE TO THE LEAVE EXISTS.
/*
Example: If a emp is on Annual leave from 01-jan-2017 to 30-jan-2017
now we are entering in SIT (Training) 10-jan-2017 to 15-jan-2017
As training days falls between the annual leave days, then the user entering the SIT will be notified by warning message.
*/
(P_PERSON_ID in NUMBER
,P_BUSINESS_GROUP_ID in NUMBER
,P_ID_FLEX_NUM in NUMBER
,P_EFFECTIVE_DATE in DATE
,P_COMMENTS in VARCHAR2
,P_DATE_FROM in DATE
,P_DATE_TO in DATE
,P_REQUEST_ID in NUMBER
,P_PROGRAM_APPLICATION_ID in NUMBER
,P_PROGRAM_ID in NUMBER
,P_PROGRAM_UPDATE_DATE in DATE
,P_ATTRIBUTE_CATEGORY in VARCHAR2
,P_ATTRIBUTE1 in VARCHAR2
,P_ATTRIBUTE2 in VARCHAR2
,P_ATTRIBUTE3 in VARCHAR2
,P_ATTRIBUTE4 in VARCHAR2
,P_ATTRIBUTE5 in VARCHAR2
,P_ATTRIBUTE6 in VARCHAR2
,P_ATTRIBUTE7 in VARCHAR2
,P_ATTRIBUTE8 in VARCHAR2
,P_ATTRIBUTE9 in VARCHAR2
,P_ATTRIBUTE10 in VARCHAR2
,P_ATTRIBUTE11 in VARCHAR2
,P_ATTRIBUTE12 in VARCHAR2
,P_ATTRIBUTE13 in VARCHAR2
,P_ATTRIBUTE14 in VARCHAR2
,P_ATTRIBUTE15 in VARCHAR2
,P_ATTRIBUTE16 in VARCHAR2
,P_ATTRIBUTE17 in VARCHAR2
,P_ATTRIBUTE18 in VARCHAR2
,P_ATTRIBUTE19 in VARCHAR2
,P_ATTRIBUTE20 in VARCHAR2
,P_SEGMENT1 in VARCHAR2
,P_SEGMENT2 in VARCHAR2
,P_SEGMENT3 in VARCHAR2
,P_SEGMENT4 in VARCHAR2
,P_SEGMENT5 in VARCHAR2
,P_SEGMENT6 in VARCHAR2
,P_SEGMENT7 in VARCHAR2
,P_SEGMENT8 in VARCHAR2
,P_SEGMENT9 in VARCHAR2
,P_SEGMENT10 in VARCHAR2
,P_SEGMENT11 in VARCHAR2
,P_SEGMENT12 in VARCHAR2
,P_SEGMENT13 in VARCHAR2
,P_SEGMENT14 in VARCHAR2
,P_SEGMENT15 in VARCHAR2
,P_SEGMENT16 in VARCHAR2
,P_SEGMENT17 in VARCHAR2
,P_SEGMENT18 in VARCHAR2
,P_SEGMENT19 in VARCHAR2
,P_SEGMENT20 in VARCHAR2
,P_SEGMENT21 in VARCHAR2
,P_SEGMENT22 in VARCHAR2
,P_SEGMENT23 in VARCHAR2
,P_SEGMENT24 in VARCHAR2
,P_SEGMENT25 in VARCHAR2
,P_SEGMENT26 in VARCHAR2
,P_SEGMENT27 in VARCHAR2
,P_SEGMENT28 in VARCHAR2
,P_SEGMENT29 in VARCHAR2
,P_SEGMENT30 in VARCHAR2
,P_CONCAT_SEGMENTS in VARCHAR2
,P_ANALYSIS_CRITERIA_ID in NUMBER
,P_PERSON_ANALYSIS_ID in NUMBER
,P_PEA_OBJECT_VERSION_NUMBER in NUMBER)
IS
L_ANL VARCHAR2(1);
BEGIN
BEGIN
IF P_ID_FLEX_NUM = 50453 --Allowance Request
THEN
XX_SIT_EVENTS.CREATE_ELEMENT_ENTRY_UH_P(P_BUSINESS_GROUP_ID, P_PERSON_ID, P_ANALYSIS_CRITERIA_ID, P_SEGMENT1, P_SEGMENT2, P_SEGMENT11);
--HR_UTILITY.SET_MESSAGE (800, 'LSG_ANN_LEAVE_GREATER_THAN_30');
--HR_UTILITY.set_warning;
--RAISE_ERROR;
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
EXCEPTION WHEN OTHERS THEN NULL;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END;
Step3:
Register user hook.
XXFUJ_CREATE_SIT_A_UHPKG.XXFUJ_CREATE_SIT_LEV_VAL
XX_SIT_EVENTS.CREATE_ELEMENT_ENTRY_UH_P
1.
SELECT ahk.api_hook_id,
ahk.api_module_id,
ahk.hook_package,
ahk.hook_procedure
FROM hr_api_hooks ahk, hr_api_modules ahm
WHERE ahm.module_name like '%CREATE_SIT%'
AND ahm.api_module_type = 'BP'
AND ahk.api_hook_type = 'AP'
AND ahk.api_module_id = ahm.api_module_id
API_HOOK_ID = 2879
API_MODULE_ID = 1279
2.
DECLARE
L_API_HOOK_ID NUMBER:= 2879;
L_API_HOOK_CALL_ID NUMBER;
L_OBJECT_VERSION_NUMBER NUMBER;
L_SEQUENCE NUMBER;
BEGIN
SELECT HR_API_HOOKS_S.NEXTVAL
INTO L_SEQUENCE
FROM DUAL;
HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL
(P_VALIDATE => FALSE,
P_EFFECTIVE_DATE => TO_DATE('01-JAN-1952','DD-MON-YYYY'),
P_API_HOOK_ID =>L_API_HOOK_ID ,
P_API_HOOK_CALL_TYPE => 'PP',
P_SEQUENCE => L_SEQUENCE,
P_ENABLED_FLAG => 'Y',
P_CALL_PACKAGE => 'XXFUJ_CREATE_SIT_A_UHPKG',
P_CALL_PROCEDURE => 'XXFUJ_CREATE_SIT_LEV_VAL',
P_API_HOOK_CALL_ID => L_API_HOOK_CALL_ID,
P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
DBMS_OUTPUT.PUT_LINE('L_API_HOOK_CALL_ID '|| L_API_HOOK_CALL_ID);
END ;
COMMIT
SELECT API_HOOK_CALL_ID,API_HOOK_ID,
OBJECT_VERSION_NUMBER,
CALL_PACKAGE,
CALL_PROCEDURE,
ENABLED_FLAG,
STATUS
FROM HR_API_HOOK_CALLS
--WHERE api_hook_id = 2878
--WHERE API_HOOK_CALL_ID = 1263
WHERE CALL_PACKAGE LIKE '%FUJ%'
commit
3.
declare
l_api_module_id number := 1279; --Value 1731 is derived from Step 1 above using following query
begin
hr_api_user_hooks_utility.create_hooks_one_module (l_api_module_id);
dbms_output.put_line('Success');
exception when others then
dbms_output.put_line('Exception : '||SQLERRM);
end;
COMMIT
To delete an user hook:
BEGIN
Hr_Api_Hook_Call_Api.delete_api_hook_call ( p_validate => FALSE,
p_api_hook_call_id => 1274,
p_object_version_number =>2
);
DBMS_OUTPUT.PUT_LINE('deleted Successfully');
END;
COMMIT
To Update an user hook:
declare
p_object_version_number number;
BEGIN
Hr_Api_Hook_Call_Api.update_api_hook_call ( p_validate =>TRUE,
p_effective_date => to_date('01-jan-2004'),
p_api_hook_call_id =>1270,
--p_sequence in number default hr_api.g_number,
--p_enabled_flag in varchar2 default hr_api.g_varchar2,
--p_call_package in varchar2 default hr_api.g_varchar2,
--p_call_procedure in varchar2 default hr_api.g_varchar2,
p_object_version_number =>p_object_version_number
);
DBMS_OUTPUT.PUT_LINE('updated Successfully');
END;
SELECT ANALYSIS_CRITERIA_ID,
ID_FLEX_NUM,
SEGMENT1 Allowance_Name,
SEGMENT2 EFFECTIVE_DATE,
SEGMENT11 AMOUNT
FROM
PER_ANALYSIS_CRITERIA
WHERE ANALYSIS_CRITERIA_ID = 4949604
SELECT ELEMENT_NAME, ELEMENT_TYPE_ID
FROM PAY_ELEMENT_TYPES_F_TL
WHERE LANGUAGE = USERENV('LANG')
AND ELEMENT_TYPE_ID IN (593,559,562,561,564,590,589,597)
Errors and Solution:
1. Don’t forget to commit the transactions in the Step3.
2. If you get the error in the HR as “ Error: run the script $PER_TOP/admin/sql/hrahkpar.sql”
Solution: Delete the user hook , commit, create again using Step3.
3. If updated the package, procedure delete again the user hook and recreate again.
4. No need to bounce the apache or middle just run the Step3 of 3rd point.
No comments:
Post a Comment