Requirement: To add Element, once the SIT is updated.
CREATE OR REPLACE PACKAGE APPS.XXFUJ_UPDATE_SIT_A_UHPKG IS
--USER HOOK PACKAGE FOR HR_SIT_BK1.CREATE_SIT_A
PROCEDURE XXFUJ_UPDATE_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_ANALYSIS_ID in NUMBER
,P_PEA_OBJECT_VERSION_NUMBER in NUMBER
,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);
END;
/
CREATE OR REPLACE PACKAGE BODY XXFUJ_UPDATE_SIT_A_UHPKG IS
--USER HOOK PACKAGE FOR HR_SIT_BK1.CREATE_SIT_A
PROCEDURE XXFUJ_UPDATE_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_ANALYSIS_ID in NUMBER
,P_PEA_OBJECT_VERSION_NUMBER in NUMBER
,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)
IS
L_ANL VARCHAR2(1);
L_ERROR VARCHAR2(200);
L_ID_FLEX_NUM VARCHAR2(200);
L_SEGMENT1 VARCHAR2(200);
L_SEGMENT2 VARCHAR2(200);
L_SEGMENT3 VARCHAR2(200);
LCAL_ST_DT DATE;
LCAL_END_DT DATE;
L_ST_DT DATE;
L_END_DT DATE;
L_JOIN_DT DATE;
L_DAYS NUMBER;
L_PERSON_ID NUMBER;
L_EFF_DATE DATE;
L_ASSIGNMENT_ID NUMBER;
LN_ELEMENT_LINK_ID NUMBER;
LD_EFFECTIVE_START_DATE DATE;
LD_EFFECTIVE_END_DATE DATE;
LN_ELEMENT_ENTRY_ID NUMBER;
LN_OBJECT_VERSION_NUMBER NUMBER;
LB_CREATE_WARNING BOOLEAN;
BEGIN
BEGIN
SELECT PAC.ID_FLEX_NUM, SEGMENT1, SEGMENT2, SEGMENT3, PERSON_ID
INTO L_ID_FLEX_NUM, L_SEGMENT1, L_SEGMENT2, L_SEGMENT3, L_PERSON_ID
FROM PER_ANALYSIS_CRITERIA PAC,
per_person_analyses ppa
WHERE PAC.ANALYSIS_CRITERIA_ID = P_ANALYSIS_CRITERIA_ID
--where pac.id_flex_num = 50318
--and pac.ANALYSIS_CRITERIA_ID = 5167442
and pac.ANALYSIS_CRITERIA_ID = ppa.ANALYSIS_CRITERIA_ID
;
IF L_ID_FLEX_NUM = 50318 THEN
select start_date, end_date
into lcal_st_dt, lcal_end_dt
from per_calendar_entries;
L_ST_DT := fnd_conc_date.string_to_date(L_SEGMENT1);
L_END_DT := fnd_conc_date.string_to_date(L_SEGMENT2);
L_JOIN_DT := fnd_conc_date.string_to_date(L_SEGMENT3);
if l_st_dt between lcal_st_dt and lcal_end_dt then
l_days := least(lcal_end_dt,l_end_dt) - l_st_dt + 1 ;
elsif
l_end_dt between lcal_st_dt and lcal_end_dt then
l_days := lcal_end_dt - l_end_dt + 1 ;
elsif lcal_end_dt between l_st_dt and l_end_dt then
l_days := lcal_end_dt - lcal_st_dt + 1;
end if;
L_EFF_DATE := TRUNC(L_JOIN_DT,'MM');
L_ASSIGNMENT_ID := XXPY_UPDATE_EMP_ASSIGNMENT.GET_EMP_ASSIGNMENT_ID(L_PERSON_ID,fnd_conc_date.string_to_date(sysdate));
IF L_DAYS >'0'
AND XX_FUJ_ENTRIES_ALLOWANCES.F_ELEMENT_EXISTS_YN(L_ASSIGNMENT_ID,'Fuj_Annual_Leave_Public_holiday','Days',L_EFF_DATE) = 'N'
THEN
--INSERT INTO TESTA VALUES (L_DAYS,'TEST'||TO_CHAR(SYSDATE,'HH:MI'));
BEGIN
LN_ELEMENT_LINK_ID :=
HR_ENTRY_API.get_link (
P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
P_ELEMENT_TYPE_ID => 2012,
P_SESSION_DATE => SYSDATE);
PAY_ELEMENT_ENTRY_API.CREATE_ELEMENT_ENTRY( -- Input data elements
-- -----------------------------
P_EFFECTIVE_DATE => L_EFF_DATE,--to_date('01-MAY-2018'),
P_BUSINESS_GROUP_ID => FND_PROFILE.VALUE ('PER_BUSINESS_GROUP_ID'),
P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
P_ELEMENT_LINK_ID => LN_ELEMENT_LINK_ID,
P_ENTRY_TYPE => 'E',
P_INPUT_VALUE_ID1 => XXPY_CONSALIDATIONS.GET_INPUT_VALUE_ID(
'Fuj_Annual_Leave_Public_holiday',
'Days'),
P_ENTRY_VALUE1 => L_DAYS,
P_INPUT_VALUE_ID2 => XXPY_CONSALIDATIONS.GET_INPUT_VALUE_ID(
'Fuj_Annual_Leave_Public_holiday',
'Date'),
P_ENTRY_VALUE2 => L_EFF_DATE,
-- 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);
END;
--ELSE INSERT INTO TESTA VALUES (L_DAYS,'EXISTS ALREADY'||TO_CHAR(SYSDATE,'HH:MI'));
END IF;
END IF;
EXCEPTION WHEN OTHERS THEN
L_ERROR := SQLERRM;
INSERT INTO TESTA
SELECT 101,L_ERROR FROM DUAL;
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
EXCEPTION WHEN OTHERS THEN NULL;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END;
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 '%UPDATE_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 = 2884-- 2879
API_MODULE_ID = 1281--1279
HR_SIT_BK2.UPDATE_SIT_A
2.
DECLARE
L_API_HOOK_ID NUMBER:= 2884;
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;
R_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_UPDATE_SIT_A_UHPKG',
P_CALL_PROCEDURE => 'XXFUJ_UPDATE_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, CREATION_DATE
--SELECT *
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 := 1281; --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
No comments:
Post a Comment