now we are going to handle User hook case and it use when we want to validate EIT Or SIT in both case Self Service or Professional View (Form based)
our steps is
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 '%PERSON_EXTRA%' --AND ahm.api_module_type = 'BP' --AND ahk.api_hook_type = 'AP' AND ahk.api_module_id = ahm.api_module_id order by HOOK_PACKAGE
our steps is
- find our standard package and make copy as xxPKG (
- query to find it
- create copy package (XXwhatever) as same parameters
- register it to let values pass in our business cases using information type
- then check it into user hooks table (HR_API_HOOK_CALLS) and make sure its valid (V) and its exist into the standard package that we copy from it
- test the case
- find ur need query => event based (after , before ) , action based (create,update,delete) as ur case
- query
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 '%PERSON_EXTRA%' --AND ahm.api_module_type = 'BP' --AND ahk.api_hook_type = 'AP' AND ahk.api_module_id = ahm.api_module_id order by HOOK_PACKAGE
- create ur package
(Header)
CREATE OR REPLACE PACKAGE APPS.XX_EIT_PERSON_USERHOOKS Is
Procedure Register_Userhooks;
Procedure Delete_Userhooks;
Procedure Before_Create_Person_EIT(p_Person_Id In Number,
p_Information_Type In Varchar2,
p_Pei_Attribute_Category In Varchar2,
p_Pei_Attribute1 In Varchar2,
p_Pei_Attribute2 In Varchar2,
p_Pei_Attribute3 In Varchar2,
p_Pei_Attribute4 In Varchar2,
p_Pei_Attribute5 In Varchar2,
p_Pei_Attribute6 In Varchar2,
p_Pei_Attribute7 In Varchar2,
p_Pei_Attribute8 In Varchar2,
p_Pei_Attribute9 In Varchar2,
p_Pei_Attribute10 In Varchar2,
p_Pei_Attribute11 In Varchar2,
p_Pei_Attribute12 In Varchar2,
p_Pei_Attribute13 In Varchar2,
p_Pei_Attribute14 In Varchar2,
p_Pei_Attribute15 In Varchar2,
p_Pei_Attribute16 In Varchar2,
p_Pei_Attribute17 In Varchar2,
p_Pei_Attribute18 In Varchar2,
p_Pei_Attribute19 In Varchar2,
p_Pei_Attribute20 In Varchar2,
p_Pei_Information_Category In Varchar2,
p_Pei_Information1 In Varchar2,
p_Pei_Information2 In Varchar2,
p_Pei_Information3 In Varchar2,
p_Pei_Information4 In Varchar2,
p_Pei_Information5 In Varchar2,
p_Pei_Information6 In Varchar2,
p_Pei_Information7 In Varchar2,
p_Pei_Information8 In Varchar2,
p_Pei_Information9 In Varchar2,
p_Pei_Information10 In Varchar2,
p_Pei_Information11 In Varchar2,
p_Pei_Information12 In Varchar2,
p_Pei_Information13 In Varchar2,
p_Pei_Information14 In Varchar2,
p_Pei_Information15 In Varchar2,
p_Pei_Information16 In Varchar2,
p_Pei_Information17 In Varchar2,
p_Pei_Information18 In Varchar2,
p_Pei_Information19 In Varchar2,
p_Pei_Information20 In Varchar2,
p_Pei_Information21 In Varchar2,
p_Pei_Information22 In Varchar2,
p_Pei_Information23 In Varchar2,
p_Pei_Information24 In Varchar2,
p_Pei_Information25 In Varchar2,
p_Pei_Information26 In Varchar2,
p_Pei_Information27 In Varchar2,
p_Pei_Information28 In Varchar2,
p_Pei_Information29 In Varchar2,
p_Pei_Information30 In Varchar2);
Procedure Before_Update_Person_EIT(p_Person_Extra_Info_Id In Number,
p_Pei_Attribute_Category In Varchar2,
p_Pei_Attribute1 In Varchar2,
p_Pei_Attribute2 In Varchar2,
p_Pei_Attribute3 In Varchar2,
p_Pei_Attribute4 In Varchar2,
p_Pei_Attribute5 In Varchar2,
p_Pei_Attribute6 In Varchar2,
p_Pei_Attribute7 In Varchar2,
p_Pei_Attribute8 In Varchar2,
p_Pei_Attribute9 In Varchar2,
p_Pei_Attribute10 In Varchar2,
p_Pei_Attribute11 In Varchar2,
p_Pei_Attribute12 In Varchar2,
p_Pei_Attribute13 In Varchar2,
p_Pei_Attribute14 In Varchar2,
p_Pei_Attribute15 In Varchar2,
p_Pei_Attribute16 In Varchar2,
p_Pei_Attribute17 In Varchar2,
p_Pei_Attribute18 In Varchar2,
p_Pei_Attribute19 In Varchar2,
p_Pei_Attribute20 In Varchar2,
p_Pei_Information_Category In Varchar2,
p_Pei_Information1 In Varchar2,
p_Pei_Information2 In Varchar2,
p_Pei_Information3 In Varchar2,
p_Pei_Information4 In Varchar2,
p_Pei_Information5 In Varchar2,
p_Pei_Information6 In Varchar2,
p_Pei_Information7 In Varchar2,
p_Pei_Information8 In Varchar2,
p_Pei_Information9 In Varchar2,
p_Pei_Information10 In Varchar2,
p_Pei_Information11 In Varchar2,
p_Pei_Information12 In Varchar2,
p_Pei_Information13 In Varchar2,
p_Pei_Information14 In Varchar2,
p_Pei_Information15 In Varchar2,
p_Pei_Information16 In Varchar2,
p_Pei_Information17 In Varchar2,
p_Pei_Information18 In Varchar2,
p_Pei_Information19 In Varchar2,
p_Pei_Information20 In Varchar2,
p_Pei_Information21 In Varchar2,
p_Pei_Information22 In Varchar2,
p_Pei_Information23 In Varchar2,
p_Pei_Information24 In Varchar2,
p_Pei_Information25 In Varchar2,
p_Pei_Information26 In Varchar2,
p_Pei_Information27 In Varchar2,
p_Pei_Information28 In Varchar2,
p_Pei_Information29 In Varchar2,
p_Pei_Information30 In Varchar2,
p_Object_Version_Number In Number);
Procedure Before_Delete_Person_EIT(p_Person_Extra_Info_Id In Number,
p_Object_Version_Number In Number);
Procedure After_Create_Person_EIT(p_Person_Extra_Info_Id In Number,
p_Person_Id In Number,
p_Information_Type In Varchar2,
p_Pei_Attribute_Category In Varchar2,
p_Pei_Attribute1 In Varchar2,
p_Pei_Attribute2 In Varchar2,
p_Pei_Attribute3 In Varchar2,
p_Pei_Attribute4 In Varchar2,
p_Pei_Attribute5 In Varchar2,
p_Pei_Attribute6 In Varchar2,
p_Pei_Attribute7 In Varchar2,
p_Pei_Attribute8 In Varchar2,
p_Pei_Attribute9 In Varchar2,
p_Pei_Attribute10 In Varchar2,
p_Pei_Attribute11 In Varchar2,
p_Pei_Attribute12 In Varchar2,
p_Pei_Attribute13 In Varchar2,
p_Pei_Attribute14 In Varchar2,
p_Pei_Attribute15 In Varchar2,
p_Pei_Attribute16 In Varchar2,
p_Pei_Attribute17 In Varchar2,
p_Pei_Attribute18 In Varchar2,
p_Pei_Attribute19 In Varchar2,
p_Pei_Attribute20 In Varchar2,
p_Pei_Information_Category In Varchar2,
p_Pei_Information1 In Varchar2,
p_Pei_Information2 In Varchar2,
p_Pei_Information3 In Varchar2,
p_Pei_Information4 In Varchar2,
p_Pei_Information5 In Varchar2,
p_Pei_Information6 In Varchar2,
p_Pei_Information7 In Varchar2,
p_Pei_Information8 In Varchar2,
p_Pei_Information9 In Varchar2,
p_Pei_Information10 In Varchar2,
p_Pei_Information11 In Varchar2,
p_Pei_Information12 In Varchar2,
p_Pei_Information13 In Varchar2,
p_Pei_Information14 In Varchar2,
p_Pei_Information15 In Varchar2,
p_Pei_Information16 In Varchar2,
p_Pei_Information17 In Varchar2,
p_Pei_Information18 In Varchar2,
p_Pei_Information19 In Varchar2,
p_Pei_Information20 In Varchar2,
p_Pei_Information21 In Varchar2,
p_Pei_Information22 In Varchar2,
p_Pei_Information23 In Varchar2,
p_Pei_Information24 In Varchar2,
p_Pei_Information25 In Varchar2,
p_Pei_Information26 In Varchar2,
p_Pei_Information27 In Varchar2,
p_Pei_Information28 In Varchar2,
p_Pei_Information29 In Varchar2,
p_Pei_Information30 In Varchar2,
p_Object_Version_Number In Number);
Procedure After_Update_Person_EIT(p_Person_Extra_Info_Id In Number,
p_Pei_Attribute_Category In Varchar2,
p_Pei_Attribute1 In Varchar2,
p_Pei_Attribute2 In Varchar2,
p_Pei_Attribute3 In Varchar2,
p_Pei_Attribute4 In Varchar2,
p_Pei_Attribute5 In Varchar2,
p_Pei_Attribute6 In Varchar2,
p_Pei_Attribute7 In Varchar2,
p_Pei_Attribute8 In Varchar2,
p_Pei_Attribute9 In Varchar2,
p_Pei_Attribute10 In Varchar2,
p_Pei_Attribute11 In Varchar2,
p_Pei_Attribute12 In Varchar2,
p_Pei_Attribute13 In Varchar2,
p_Pei_Attribute14 In Varchar2,
p_Pei_Attribute15 In Varchar2,
p_Pei_Attribute16 In Varchar2,
p_Pei_Attribute17 In Varchar2,
p_Pei_Attribute18 In Varchar2,
p_Pei_Attribute19 In Varchar2,
p_Pei_Attribute20 In Varchar2,
p_Pei_Information_Category In Varchar2,
p_Pei_Information1 In Varchar2,
p_Pei_Information2 In Varchar2,
p_Pei_Information3 In Varchar2,
p_Pei_Information4 In Varchar2,
p_Pei_Information5 In Varchar2,
p_Pei_Information6 In Varchar2,
p_Pei_Information7 In Varchar2,
p_Pei_Information8 In Varchar2,
p_Pei_Information9 In Varchar2,
p_Pei_Information10 In Varchar2,
p_Pei_Information11 In Varchar2,
p_Pei_Information12 In Varchar2,
p_Pei_Information13 In Varchar2,
p_Pei_Information14 In Varchar2,
p_Pei_Information15 In Varchar2,
p_Pei_Information16 In Varchar2,
p_Pei_Information17 In Varchar2,
p_Pei_Information18 In Varchar2,
p_Pei_Information19 In Varchar2,
p_Pei_Information20 In Varchar2,
p_Pei_Information21 In Varchar2,
p_Pei_Information22 In Varchar2,
p_Pei_Information23 In Varchar2,
p_Pei_Information24 In Varchar2,
p_Pei_Information25 In Varchar2,
p_Pei_Information26 In Varchar2,
p_Pei_Information27 In Varchar2,
p_Pei_Information28 In Varchar2,
p_Pei_Information29 In Varchar2,
p_Pei_Information30 In Varchar2,
p_Object_Version_Number In Number);
Procedure After_Delete_Person_EIT(p_Person_Extra_Info_Id In Number,
p_Object_Version_Number In Number);
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE CALL_EITS (P_EIT_REC PER_PEOPLE_EXTRA_INFO%ROWTYPE , P_EVENT_TIME VARCHAR, P_EVENT_MODE VARCHAR2);
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE EIT_BANK_COMMITMENT (P_EIT_REC PER_PEOPLE_EXTRA_INFO%ROWTYPE , P_EVENT_TIME VARCHAR, P_EVENT_MODE VARCHAR2);
----------------------------------------------------------------------------------------------------------------------------------
FUNCTION GET_PAYMENT_DATE (P_PERIOD VARCHAR2) RETURN DATE;
FUNCTION DELETE_ELEMENT(P_PERSON_ID NUMBER, P_EIT_REQUEST_ID NUMBER, P_ELEMENT_NAME VARCHAR2) RETURN NUMBER;
FUNCTION TEMP(P_NAME VARCHAR2) RETURN VARCHAR2;
End XX_EIT_PERSON_USERHOOKS;
/
(Body)
CREATE OR REPLACE PACKAGE BODY APPS.XX_EIT_PERSON_USERHOOKS
AS
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE Register_Userhooks
AS
BEGIN
XX_MAINTAIN_USERHOOKS.REGISTER_USER_HOOK ('HR_PERSON_EXTRA_INFO_BK1',
'CREATE_PERSON_EXTRA_INFO_B',
4051,
'XX_EIT_PERSON_USERHOOKS',
'Before_Create_Person_EIT');
XX_MAINTAIN_USERHOOKS.REGISTER_USER_HOOK ('HR_PERSON_EXTRA_INFO_BK2',
'UPDATE_PERSON_EXTRA_INFO_B',
4052,
'XX_EIT_PERSON_USERHOOKS',
'Before_Update_Person_EIT');
XX_MAINTAIN_USERHOOKS.REGISTER_USER_HOOK ('HR_PERSON_EXTRA_INFO_BK3',
'DELETE_PERSON_EXTRA_INFO_B',
4053,
'XX_EIT_PERSON_USERHOOKS',
'Before_Delete_Person_EIT');
XX_MAINTAIN_USERHOOKS.REGISTER_USER_HOOK ('HR_PERSON_EXTRA_INFO_BK1',
'CREATE_PERSON_EXTRA_INFO_A',
4054,
'XX_EIT_PERSON_USERHOOKS',
'After_Create_Person_EIT');
XX_MAINTAIN_USERHOOKS.REGISTER_USER_HOOK ('HR_PERSON_EXTRA_INFO_BK2',
'UPDATE_PERSON_EXTRA_INFO_A',
4055,
'XX_EIT_PERSON_USERHOOKS',
'After_Update_Person_EIT');
XX_MAINTAIN_USERHOOKS.REGISTER_USER_HOOK ('HR_PERSON_EXTRA_INFO_BK3',
'DELETE_PERSON_EXTRA_INFO_A',
4056,
'XX_EIT_PERSON_USERHOOKS',
'After_Delete_Person_EIT');
XX_MAINTAIN_USERHOOKS.HOOK_PREPROCESSOR ('HR_PERSON_EXTRA_INFO_BK1');
XX_MAINTAIN_USERHOOKS.HOOK_PREPROCESSOR ('HR_PERSON_EXTRA_INFO_BK2');
XX_MAINTAIN_USERHOOKS.HOOK_PREPROCESSOR ('HR_PERSON_EXTRA_INFO_BK3');
/*
Calling:
execute XX_EIT_PERSON_USERHOOKS.Register_Userhooks;
---------------------------------------
Validate result
SELECT * FROM HR_API_HOOK_CALLS
WHERE CALL_PACKAGE = 'XX_EIT_PERSON_USERHOOKS';
*/
END Register_Userhooks;
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE Delete_Userhooks
AS
BEGIN
/* TODO implementation required */
/*query numbers by this code
SELECT API_HOOK_CALL_ID , OBJECT_VERSION_NUMBER
FROM HR_API_HOOK_CALLS
WHERE API_HOOK_ID = 2758 --ID
AND CALL_PACKAGE = 'XX_EIT_PERSON_USERHOOKS'
AND CALL_PROCEDURE = '????'
*/
/*
XX_MAINTAIN_USERHOOKS.DELETE_USER_HOOK(1220,1);
XX_MAINTAIN_USERHOOKS.DELETE_USER_HOOK(1221,1);
XX_MAINTAIN_USERHOOKS.DELETE_USER_HOOK(1222,1);
XX_MAINTAIN_USERHOOKS.DELETE_USER_HOOK(1223,1);
XX_MAINTAIN_USERHOOKS.DELETE_USER_HOOK(1224,1);
XX_MAINTAIN_USERHOOKS.DELETE_USER_HOOK(1225,1);
*/
NULL;
END Delete_Userhooks;
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE Before_Create_Person_EIT (
p_Person_Id IN NUMBER,
p_Information_Type IN VARCHAR2,
p_Pei_Attribute_Category IN VARCHAR2,
p_Pei_Attribute1 IN VARCHAR2,
p_Pei_Attribute2 IN VARCHAR2,
p_Pei_Attribute3 IN VARCHAR2,
p_Pei_Attribute4 IN VARCHAR2,
p_Pei_Attribute5 IN VARCHAR2,
p_Pei_Attribute6 IN VARCHAR2,
p_Pei_Attribute7 IN VARCHAR2,
p_Pei_Attribute8 IN VARCHAR2,
p_Pei_Attribute9 IN VARCHAR2,
p_Pei_Attribute10 IN VARCHAR2,
p_Pei_Attribute11 IN VARCHAR2,
p_Pei_Attribute12 IN VARCHAR2,
p_Pei_Attribute13 IN VARCHAR2,
p_Pei_Attribute14 IN VARCHAR2,
p_Pei_Attribute15 IN VARCHAR2,
p_Pei_Attribute16 IN VARCHAR2,
p_Pei_Attribute17 IN VARCHAR2,
p_Pei_Attribute18 IN VARCHAR2,
p_Pei_Attribute19 IN VARCHAR2,
p_Pei_Attribute20 IN VARCHAR2,
p_Pei_Information_Category IN VARCHAR2,
p_Pei_Information1 IN VARCHAR2,
p_Pei_Information2 IN VARCHAR2,
p_Pei_Information3 IN VARCHAR2,
p_Pei_Information4 IN VARCHAR2,
p_Pei_Information5 IN VARCHAR2,
p_Pei_Information6 IN VARCHAR2,
p_Pei_Information7 IN VARCHAR2,
p_Pei_Information8 IN VARCHAR2,
p_Pei_Information9 IN VARCHAR2,
p_Pei_Information10 IN VARCHAR2,
p_Pei_Information11 IN VARCHAR2,
p_Pei_Information12 IN VARCHAR2,
p_Pei_Information13 IN VARCHAR2,
p_Pei_Information14 IN VARCHAR2,
p_Pei_Information15 IN VARCHAR2,
p_Pei_Information16 IN VARCHAR2,
p_Pei_Information17 IN VARCHAR2,
p_Pei_Information18 IN VARCHAR2,
p_Pei_Information19 IN VARCHAR2,
p_Pei_Information20 IN VARCHAR2,
p_Pei_Information21 IN VARCHAR2,
p_Pei_Information22 IN VARCHAR2,
p_Pei_Information23 IN VARCHAR2,
p_Pei_Information24 IN VARCHAR2,
p_Pei_Information25 IN VARCHAR2,
p_Pei_Information26 IN VARCHAR2,
p_Pei_Information27 IN VARCHAR2,
p_Pei_Information28 IN VARCHAR2,
p_Pei_Information29 IN VARCHAR2,
p_Pei_Information30 IN VARCHAR2)
AS
v_INFO_Rec PER_PEOPLE_EXTRA_INFO%ROWTYPE;
BEGIN
v_INFO_Rec.Person_Id := p_Person_Id;
v_INFO_Rec.Information_Type := p_Information_Type;
v_INFO_Rec.Pei_Attribute_Category := p_Pei_Attribute_Category;
v_INFO_Rec.Pei_Attribute1 := p_Pei_Attribute1;
v_INFO_Rec.Pei_Attribute2 := p_Pei_Attribute2;
v_INFO_Rec.Pei_Attribute3 := p_Pei_Attribute3;
v_INFO_Rec.Pei_Attribute4 := p_Pei_Attribute4;
v_INFO_Rec.Pei_Attribute5 := p_Pei_Attribute5;
v_INFO_Rec.Pei_Attribute6 := p_Pei_Attribute6;
v_INFO_Rec.Pei_Attribute7 := p_Pei_Attribute7;
v_INFO_Rec.Pei_Attribute8 := p_Pei_Attribute8;
v_INFO_Rec.Pei_Attribute9 := p_Pei_Attribute9;
v_INFO_Rec.Pei_Attribute10 := p_Pei_Attribute10;
v_INFO_Rec.Pei_Attribute11 := p_Pei_Attribute11;
v_INFO_Rec.Pei_Attribute12 := p_Pei_Attribute12;
v_INFO_Rec.Pei_Attribute13 := p_Pei_Attribute13;
v_INFO_Rec.Pei_Attribute14 := p_Pei_Attribute14;
v_INFO_Rec.Pei_Attribute15 := p_Pei_Attribute15;
v_INFO_Rec.Pei_Attribute16 := p_Pei_Attribute16;
v_INFO_Rec.Pei_Attribute17 := p_Pei_Attribute17;
v_INFO_Rec.Pei_Attribute18 := p_Pei_Attribute18;
v_INFO_Rec.Pei_Attribute19 := p_Pei_Attribute19;
v_INFO_Rec.Pei_Attribute20 := p_Pei_Attribute20;
v_INFO_Rec.Pei_Information_Category := p_Pei_Information_Category;
v_INFO_Rec.Pei_Information1 := p_Pei_Information1;
v_INFO_Rec.Pei_Information2 := p_Pei_Information2;
v_INFO_Rec.Pei_Information3 := p_Pei_Information3;
v_INFO_Rec.Pei_Information4 := p_Pei_Information4;
v_INFO_Rec.Pei_Information5 := p_Pei_Information5;
v_INFO_Rec.Pei_Information6 := p_Pei_Information6;
v_INFO_Rec.Pei_Information7 := p_Pei_Information7;
v_INFO_Rec.Pei_Information8 := p_Pei_Information8;
v_INFO_Rec.Pei_Information9 := p_Pei_Information9;
v_INFO_Rec.Pei_Information10 := p_Pei_Information10;
v_INFO_Rec.Pei_Information11 := p_Pei_Information11;
v_INFO_Rec.Pei_Information12 := p_Pei_Information12;
v_INFO_Rec.Pei_Information13 := p_Pei_Information13;
v_INFO_Rec.Pei_Information14 := p_Pei_Information14;
v_INFO_Rec.Pei_Information15 := p_Pei_Information15;
v_INFO_Rec.Pei_Information16 := p_Pei_Information16;
v_INFO_Rec.Pei_Information17 := p_Pei_Information17;
v_INFO_Rec.Pei_Information18 := p_Pei_Information18;
v_INFO_Rec.Pei_Information19 := p_Pei_Information19;
v_INFO_Rec.Pei_Information20 := p_Pei_Information20;
v_INFO_Rec.Pei_Information21 := p_Pei_Information21;
v_INFO_Rec.Pei_Information22 := p_Pei_Information22;
v_INFO_Rec.Pei_Information23 := p_Pei_Information23;
v_INFO_Rec.Pei_Information24 := p_Pei_Information24;
v_INFO_Rec.Pei_Information25 := p_Pei_Information25;
v_INFO_Rec.Pei_Information26 := p_Pei_Information26;
v_INFO_Rec.Pei_Information27 := p_Pei_Information27;
v_INFO_Rec.Pei_Information28 := p_Pei_Information28;
v_INFO_Rec.Pei_Information29 := p_Pei_Information29;
v_INFO_Rec.Pei_Information30 := p_Pei_Information30;
CALL_EITS (v_INFO_Rec, 'BEFORE', 'CREATE');
RETURN;
END Before_Create_Person_EIT;
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE Before_Update_Person_EIT (
p_Person_Extra_Info_Id IN NUMBER,
p_Pei_Attribute_Category IN VARCHAR2,
p_Pei_Attribute1 IN VARCHAR2,
p_Pei_Attribute2 IN VARCHAR2,
p_Pei_Attribute3 IN VARCHAR2,
p_Pei_Attribute4 IN VARCHAR2,
p_Pei_Attribute5 IN VARCHAR2,
p_Pei_Attribute6 IN VARCHAR2,
p_Pei_Attribute7 IN VARCHAR2,
p_Pei_Attribute8 IN VARCHAR2,
p_Pei_Attribute9 IN VARCHAR2,
p_Pei_Attribute10 IN VARCHAR2,
p_Pei_Attribute11 IN VARCHAR2,
p_Pei_Attribute12 IN VARCHAR2,
p_Pei_Attribute13 IN VARCHAR2,
p_Pei_Attribute14 IN VARCHAR2,
p_Pei_Attribute15 IN VARCHAR2,
p_Pei_Attribute16 IN VARCHAR2,
p_Pei_Attribute17 IN VARCHAR2,
p_Pei_Attribute18 IN VARCHAR2,
p_Pei_Attribute19 IN VARCHAR2,
p_Pei_Attribute20 IN VARCHAR2,
p_Pei_Information_Category IN VARCHAR2,
p_Pei_Information1 IN VARCHAR2,
p_Pei_Information2 IN VARCHAR2,
p_Pei_Information3 IN VARCHAR2,
p_Pei_Information4 IN VARCHAR2,
p_Pei_Information5 IN VARCHAR2,
p_Pei_Information6 IN VARCHAR2,
p_Pei_Information7 IN VARCHAR2,
p_Pei_Information8 IN VARCHAR2,
p_Pei_Information9 IN VARCHAR2,
p_Pei_Information10 IN VARCHAR2,
p_Pei_Information11 IN VARCHAR2,
p_Pei_Information12 IN VARCHAR2,
p_Pei_Information13 IN VARCHAR2,
p_Pei_Information14 IN VARCHAR2,
p_Pei_Information15 IN VARCHAR2,
p_Pei_Information16 IN VARCHAR2,
p_Pei_Information17 IN VARCHAR2,
p_Pei_Information18 IN VARCHAR2,
p_Pei_Information19 IN VARCHAR2,
p_Pei_Information20 IN VARCHAR2,
p_Pei_Information21 IN VARCHAR2,
p_Pei_Information22 IN VARCHAR2,
p_Pei_Information23 IN VARCHAR2,
p_Pei_Information24 IN VARCHAR2,
p_Pei_Information25 IN VARCHAR2,
p_Pei_Information26 IN VARCHAR2,
p_Pei_Information27 IN VARCHAR2,
p_Pei_Information28 IN VARCHAR2,
p_Pei_Information29 IN VARCHAR2,
p_Pei_Information30 IN VARCHAR2,
p_Object_Version_Number IN NUMBER)
AS
v_person_id PER_PEOPLE_EXTRA_INFO.PERSON_ID%TYPE;
v_information_type PER_PEOPLE_EXTRA_INFO.Information_Type%TYPE;
v_INFO_Rec PER_PEOPLE_EXTRA_INFO%ROWTYPE;
BEGIN
SELECT ppei.person_id, ppei.information_type
INTO v_person_id, v_information_type
FROM PER_PEOPLE_EXTRA_INFO ppei
WHERE ppei.person_extra_info_id = P_PERSON_EXTRA_INFO_ID;
v_INFO_Rec.Person_Extra_Info_Id := p_Person_Extra_Info_Id;
v_INFO_Rec.Person_Id := v_Person_Id;
v_INFO_Rec.Information_Type := v_Information_Type;
v_INFO_Rec.Pei_Attribute_Category := p_Pei_Attribute_Category;
v_INFO_Rec.Pei_Attribute1 := p_Pei_Attribute1;
v_INFO_Rec.Pei_Attribute2 := p_Pei_Attribute2;
v_INFO_Rec.Pei_Attribute3 := p_Pei_Attribute3;
v_INFO_Rec.Pei_Attribute4 := p_Pei_Attribute4;
v_INFO_Rec.Pei_Attribute5 := p_Pei_Attribute5;
v_INFO_Rec.Pei_Attribute6 := p_Pei_Attribute6;
v_INFO_Rec.Pei_Attribute7 := p_Pei_Attribute7;
v_INFO_Rec.Pei_Attribute8 := p_Pei_Attribute8;
v_INFO_Rec.Pei_Attribute9 := p_Pei_Attribute9;
v_INFO_Rec.Pei_Attribute10 := p_Pei_Attribute10;
v_INFO_Rec.Pei_Attribute11 := p_Pei_Attribute11;
v_INFO_Rec.Pei_Attribute12 := p_Pei_Attribute12;
v_INFO_Rec.Pei_Attribute13 := p_Pei_Attribute13;
v_INFO_Rec.Pei_Attribute14 := p_Pei_Attribute14;
v_INFO_Rec.Pei_Attribute15 := p_Pei_Attribute15;
v_INFO_Rec.Pei_Attribute16 := p_Pei_Attribute16;
v_INFO_Rec.Pei_Attribute17 := p_Pei_Attribute17;
v_INFO_Rec.Pei_Attribute18 := p_Pei_Attribute18;
v_INFO_Rec.Pei_Attribute19 := p_Pei_Attribute19;
v_INFO_Rec.Pei_Attribute20 := p_Pei_Attribute20;
v_INFO_Rec.Pei_Information_Category := p_Pei_Information_Category;
v_INFO_Rec.Pei_Information1 := p_Pei_Information1;
v_INFO_Rec.Pei_Information2 := p_Pei_Information2;
v_INFO_Rec.Pei_Information3 := p_Pei_Information3;
v_INFO_Rec.Pei_Information4 := p_Pei_Information4;
v_INFO_Rec.Pei_Information5 := p_Pei_Information5;
v_INFO_Rec.Pei_Information6 := p_Pei_Information6;
v_INFO_Rec.Pei_Information7 := p_Pei_Information7;
v_INFO_Rec.Pei_Information8 := p_Pei_Information8;
v_INFO_Rec.Pei_Information9 := p_Pei_Information9;
v_INFO_Rec.Pei_Information10 := p_Pei_Information10;
v_INFO_Rec.Pei_Information11 := p_Pei_Information11;
v_INFO_Rec.Pei_Information12 := p_Pei_Information12;
v_INFO_Rec.Pei_Information13 := p_Pei_Information13;
v_INFO_Rec.Pei_Information14 := p_Pei_Information14;
v_INFO_Rec.Pei_Information15 := p_Pei_Information15;
v_INFO_Rec.Pei_Information16 := p_Pei_Information16;
v_INFO_Rec.Pei_Information17 := p_Pei_Information17;
v_INFO_Rec.Pei_Information18 := p_Pei_Information18;
v_INFO_Rec.Pei_Information19 := p_Pei_Information19;
v_INFO_Rec.Pei_Information20 := p_Pei_Information20;
v_INFO_Rec.Pei_Information21 := p_Pei_Information21;
v_INFO_Rec.Pei_Information22 := p_Pei_Information22;
v_INFO_Rec.Pei_Information23 := p_Pei_Information23;
v_INFO_Rec.Pei_Information24 := p_Pei_Information24;
v_INFO_Rec.Pei_Information25 := p_Pei_Information25;
v_INFO_Rec.Pei_Information26 := p_Pei_Information26;
v_INFO_Rec.Pei_Information27 := p_Pei_Information27;
v_INFO_Rec.Pei_Information28 := p_Pei_Information28;
v_INFO_Rec.Pei_Information29 := p_Pei_Information29;
v_INFO_Rec.Pei_Information30 := p_Pei_Information30;
CALL_EITS (v_INFO_Rec, 'BEFORE', 'UPDATE');
RETURN;
END Before_Update_Person_EIT;
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE Before_Delete_Person_EIT (p_Person_Extra_Info_Id IN NUMBER,
p_Object_Version_Number IN NUMBER)
AS
v_INFO_Rec PER_PEOPLE_EXTRA_INFO%ROWTYPE;
BEGIN
SELECT *
INTO v_INFO_Rec
FROM PER_PEOPLE_EXTRA_INFO ppei
WHERE ppei.person_extra_info_id = P_PERSON_EXTRA_INFO_ID;
CALL_EITS (v_INFO_Rec, 'BEFORE', 'DELETE');
RETURN;
NULL;
END Before_Delete_Person_EIT;
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE After_Create_Person_EIT (
p_Person_Extra_Info_Id IN NUMBER,
p_Person_Id IN NUMBER,
p_Information_Type IN VARCHAR2,
p_Pei_Attribute_Category IN VARCHAR2,
p_Pei_Attribute1 IN VARCHAR2,
p_Pei_Attribute2 IN VARCHAR2,
p_Pei_Attribute3 IN VARCHAR2,
p_Pei_Attribute4 IN VARCHAR2,
p_Pei_Attribute5 IN VARCHAR2,
p_Pei_Attribute6 IN VARCHAR2,
p_Pei_Attribute7 IN VARCHAR2,
p_Pei_Attribute8 IN VARCHAR2,
p_Pei_Attribute9 IN VARCHAR2,
p_Pei_Attribute10 IN VARCHAR2,
p_Pei_Attribute11 IN VARCHAR2,
p_Pei_Attribute12 IN VARCHAR2,
p_Pei_Attribute13 IN VARCHAR2,
p_Pei_Attribute14 IN VARCHAR2,
p_Pei_Attribute15 IN VARCHAR2,
p_Pei_Attribute16 IN VARCHAR2,
p_Pei_Attribute17 IN VARCHAR2,
p_Pei_Attribute18 IN VARCHAR2,
p_Pei_Attribute19 IN VARCHAR2,
p_Pei_Attribute20 IN VARCHAR2,
p_Pei_Information_Category IN VARCHAR2,
p_Pei_Information1 IN VARCHAR2,
p_Pei_Information2 IN VARCHAR2,
p_Pei_Information3 IN VARCHAR2,
p_Pei_Information4 IN VARCHAR2,
p_Pei_Information5 IN VARCHAR2,
p_Pei_Information6 IN VARCHAR2,
p_Pei_Information7 IN VARCHAR2,
p_Pei_Information8 IN VARCHAR2,
p_Pei_Information9 IN VARCHAR2,
p_Pei_Information10 IN VARCHAR2,
p_Pei_Information11 IN VARCHAR2,
p_Pei_Information12 IN VARCHAR2,
p_Pei_Information13 IN VARCHAR2,
p_Pei_Information14 IN VARCHAR2,
p_Pei_Information15 IN VARCHAR2,
p_Pei_Information16 IN VARCHAR2,
p_Pei_Information17 IN VARCHAR2,
p_Pei_Information18 IN VARCHAR2,
p_Pei_Information19 IN VARCHAR2,
p_Pei_Information20 IN VARCHAR2,
p_Pei_Information21 IN VARCHAR2,
p_Pei_Information22 IN VARCHAR2,
p_Pei_Information23 IN VARCHAR2,
p_Pei_Information24 IN VARCHAR2,
p_Pei_Information25 IN VARCHAR2,
p_Pei_Information26 IN VARCHAR2,
p_Pei_Information27 IN VARCHAR2,
p_Pei_Information28 IN VARCHAR2,
p_Pei_Information29 IN VARCHAR2,
p_Pei_Information30 IN VARCHAR2,
p_Object_Version_Number IN NUMBER)
AS
v_INFO_Rec PER_PEOPLE_EXTRA_INFO%ROWTYPE;
BEGIN
v_INFO_Rec.Person_Extra_Info_Id := p_Person_Extra_Info_Id;
v_INFO_Rec.Person_Id := p_Person_Id;
v_INFO_Rec.Information_Type := p_Information_Type;
v_INFO_Rec.Pei_Attribute_Category := p_Pei_Attribute_Category;
v_INFO_Rec.Pei_Attribute1 := p_Pei_Attribute1;
v_INFO_Rec.Pei_Attribute2 := p_Pei_Attribute2;
v_INFO_Rec.Pei_Attribute3 := p_Pei_Attribute3;
v_INFO_Rec.Pei_Attribute4 := p_Pei_Attribute4;
v_INFO_Rec.Pei_Attribute5 := p_Pei_Attribute5;
v_INFO_Rec.Pei_Attribute6 := p_Pei_Attribute6;
v_INFO_Rec.Pei_Attribute7 := p_Pei_Attribute7;
v_INFO_Rec.Pei_Attribute8 := p_Pei_Attribute8;
v_INFO_Rec.Pei_Attribute9 := p_Pei_Attribute9;
v_INFO_Rec.Pei_Attribute10 := p_Pei_Attribute10;
v_INFO_Rec.Pei_Attribute11 := p_Pei_Attribute11;
v_INFO_Rec.Pei_Attribute12 := p_Pei_Attribute12;
v_INFO_Rec.Pei_Attribute13 := p_Pei_Attribute13;
v_INFO_Rec.Pei_Attribute14 := p_Pei_Attribute14;
v_INFO_Rec.Pei_Attribute15 := p_Pei_Attribute15;
v_INFO_Rec.Pei_Attribute16 := p_Pei_Attribute16;
v_INFO_Rec.Pei_Attribute17 := p_Pei_Attribute17;
v_INFO_Rec.Pei_Attribute18 := p_Pei_Attribute18;
v_INFO_Rec.Pei_Attribute19 := p_Pei_Attribute19;
v_INFO_Rec.Pei_Attribute20 := p_Pei_Attribute20;
v_INFO_Rec.Pei_Information_Category := p_Pei_Information_Category;
v_INFO_Rec.Pei_Information1 := p_Pei_Information1;
v_INFO_Rec.Pei_Information2 := p_Pei_Information2;
v_INFO_Rec.Pei_Information3 := p_Pei_Information3;
v_INFO_Rec.Pei_Information4 := p_Pei_Information4;
v_INFO_Rec.Pei_Information5 := p_Pei_Information5;
v_INFO_Rec.Pei_Information6 := p_Pei_Information6;
v_INFO_Rec.Pei_Information7 := p_Pei_Information7;
v_INFO_Rec.Pei_Information8 := p_Pei_Information8;
v_INFO_Rec.Pei_Information9 := p_Pei_Information9;
v_INFO_Rec.Pei_Information10 := p_Pei_Information10;
v_INFO_Rec.Pei_Information11 := p_Pei_Information11;
v_INFO_Rec.Pei_Information12 := p_Pei_Information12;
v_INFO_Rec.Pei_Information13 := p_Pei_Information13;
v_INFO_Rec.Pei_Information14 := p_Pei_Information14;
v_INFO_Rec.Pei_Information15 := p_Pei_Information15;
v_INFO_Rec.Pei_Information16 := p_Pei_Information16;
v_INFO_Rec.Pei_Information17 := p_Pei_Information17;
v_INFO_Rec.Pei_Information18 := p_Pei_Information18;
v_INFO_Rec.Pei_Information19 := p_Pei_Information19;
v_INFO_Rec.Pei_Information20 := p_Pei_Information20;
v_INFO_Rec.Pei_Information21 := p_Pei_Information21;
v_INFO_Rec.Pei_Information22 := p_Pei_Information22;
v_INFO_Rec.Pei_Information23 := p_Pei_Information23;
v_INFO_Rec.Pei_Information24 := p_Pei_Information24;
v_INFO_Rec.Pei_Information25 := p_Pei_Information25;
v_INFO_Rec.Pei_Information26 := p_Pei_Information26;
v_INFO_Rec.Pei_Information27 := p_Pei_Information27;
v_INFO_Rec.Pei_Information28 := p_Pei_Information28;
v_INFO_Rec.Pei_Information29 := p_Pei_Information29;
v_INFO_Rec.Pei_Information30 := p_Pei_Information30;
--RAISE_APPLICATION_ERROR(-20101, P_EIT_REC.PERSON_EXTRA_INFO_ID);
CALL_EITS (v_INFO_Rec, 'AFTER', 'CREATE');
RETURN;
END After_Create_Person_EIT;
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE After_Update_Person_EIT (
p_Person_Extra_Info_Id IN NUMBER,
p_Pei_Attribute_Category IN VARCHAR2,
p_Pei_Attribute1 IN VARCHAR2,
p_Pei_Attribute2 IN VARCHAR2,
p_Pei_Attribute3 IN VARCHAR2,
p_Pei_Attribute4 IN VARCHAR2,
p_Pei_Attribute5 IN VARCHAR2,
p_Pei_Attribute6 IN VARCHAR2,
p_Pei_Attribute7 IN VARCHAR2,
p_Pei_Attribute8 IN VARCHAR2,
p_Pei_Attribute9 IN VARCHAR2,
p_Pei_Attribute10 IN VARCHAR2,
p_Pei_Attribute11 IN VARCHAR2,
p_Pei_Attribute12 IN VARCHAR2,
p_Pei_Attribute13 IN VARCHAR2,
p_Pei_Attribute14 IN VARCHAR2,
p_Pei_Attribute15 IN VARCHAR2,
p_Pei_Attribute16 IN VARCHAR2,
p_Pei_Attribute17 IN VARCHAR2,
p_Pei_Attribute18 IN VARCHAR2,
p_Pei_Attribute19 IN VARCHAR2,
p_Pei_Attribute20 IN VARCHAR2,
p_Pei_Information_Category IN VARCHAR2,
p_Pei_Information1 IN VARCHAR2,
p_Pei_Information2 IN VARCHAR2,
p_Pei_Information3 IN VARCHAR2,
p_Pei_Information4 IN VARCHAR2,
p_Pei_Information5 IN VARCHAR2,
p_Pei_Information6 IN VARCHAR2,
p_Pei_Information7 IN VARCHAR2,
p_Pei_Information8 IN VARCHAR2,
p_Pei_Information9 IN VARCHAR2,
p_Pei_Information10 IN VARCHAR2,
p_Pei_Information11 IN VARCHAR2,
p_Pei_Information12 IN VARCHAR2,
p_Pei_Information13 IN VARCHAR2,
p_Pei_Information14 IN VARCHAR2,
p_Pei_Information15 IN VARCHAR2,
p_Pei_Information16 IN VARCHAR2,
p_Pei_Information17 IN VARCHAR2,
p_Pei_Information18 IN VARCHAR2,
p_Pei_Information19 IN VARCHAR2,
p_Pei_Information20 IN VARCHAR2,
p_Pei_Information21 IN VARCHAR2,
p_Pei_Information22 IN VARCHAR2,
p_Pei_Information23 IN VARCHAR2,
p_Pei_Information24 IN VARCHAR2,
p_Pei_Information25 IN VARCHAR2,
p_Pei_Information26 IN VARCHAR2,
p_Pei_Information27 IN VARCHAR2,
p_Pei_Information28 IN VARCHAR2,
p_Pei_Information29 IN VARCHAR2,
p_Pei_Information30 IN VARCHAR2,
p_Object_Version_Number IN NUMBER)
AS
v_person_id PER_PEOPLE_EXTRA_INFO.PERSON_ID%TYPE;
v_information_type PER_PEOPLE_EXTRA_INFO.Information_Type%TYPE;
v_INFO_Rec PER_PEOPLE_EXTRA_INFO%ROWTYPE;
BEGIN
SELECT ppei.person_id, ppei.information_type
INTO v_person_id, v_information_type
FROM PER_PEOPLE_EXTRA_INFO ppei
WHERE ppei.person_extra_info_id = P_PERSON_EXTRA_INFO_ID;
v_INFO_Rec.Person_Extra_Info_Id := p_Person_Extra_Info_Id;
v_INFO_Rec.Person_Id := v_Person_Id;
v_INFO_Rec.Information_Type := v_Information_Type;
v_INFO_Rec.Pei_Attribute_Category := p_Pei_Attribute_Category;
v_INFO_Rec.Pei_Attribute1 := p_Pei_Attribute1;
v_INFO_Rec.Pei_Attribute2 := p_Pei_Attribute2;
v_INFO_Rec.Pei_Attribute3 := p_Pei_Attribute3;
v_INFO_Rec.Pei_Attribute4 := p_Pei_Attribute4;
v_INFO_Rec.Pei_Attribute5 := p_Pei_Attribute5;
v_INFO_Rec.Pei_Attribute6 := p_Pei_Attribute6;
v_INFO_Rec.Pei_Attribute7 := p_Pei_Attribute7;
v_INFO_Rec.Pei_Attribute8 := p_Pei_Attribute8;
v_INFO_Rec.Pei_Attribute9 := p_Pei_Attribute9;
v_INFO_Rec.Pei_Attribute10 := p_Pei_Attribute10;
v_INFO_Rec.Pei_Attribute11 := p_Pei_Attribute11;
v_INFO_Rec.Pei_Attribute12 := p_Pei_Attribute12;
v_INFO_Rec.Pei_Attribute13 := p_Pei_Attribute13;
v_INFO_Rec.Pei_Attribute14 := p_Pei_Attribute14;
v_INFO_Rec.Pei_Attribute15 := p_Pei_Attribute15;
v_INFO_Rec.Pei_Attribute16 := p_Pei_Attribute16;
v_INFO_Rec.Pei_Attribute17 := p_Pei_Attribute17;
v_INFO_Rec.Pei_Attribute18 := p_Pei_Attribute18;
v_INFO_Rec.Pei_Attribute19 := p_Pei_Attribute19;
v_INFO_Rec.Pei_Attribute20 := p_Pei_Attribute20;
v_INFO_Rec.Pei_Information_Category := p_Pei_Information_Category;
v_INFO_Rec.Pei_Information1 := p_Pei_Information1;
v_INFO_Rec.Pei_Information2 := p_Pei_Information2;
v_INFO_Rec.Pei_Information3 := p_Pei_Information3;
v_INFO_Rec.Pei_Information4 := p_Pei_Information4;
v_INFO_Rec.Pei_Information5 := p_Pei_Information5;
v_INFO_Rec.Pei_Information6 := p_Pei_Information6;
v_INFO_Rec.Pei_Information7 := p_Pei_Information7;
v_INFO_Rec.Pei_Information8 := p_Pei_Information8;
v_INFO_Rec.Pei_Information9 := p_Pei_Information9;
v_INFO_Rec.Pei_Information10 := p_Pei_Information10;
v_INFO_Rec.Pei_Information11 := p_Pei_Information11;
v_INFO_Rec.Pei_Information12 := p_Pei_Information12;
v_INFO_Rec.Pei_Information13 := p_Pei_Information13;
v_INFO_Rec.Pei_Information14 := p_Pei_Information14;
v_INFO_Rec.Pei_Information15 := p_Pei_Information15;
v_INFO_Rec.Pei_Information16 := p_Pei_Information16;
v_INFO_Rec.Pei_Information17 := p_Pei_Information17;
v_INFO_Rec.Pei_Information18 := p_Pei_Information18;
v_INFO_Rec.Pei_Information19 := p_Pei_Information19;
v_INFO_Rec.Pei_Information20 := p_Pei_Information20;
v_INFO_Rec.Pei_Information21 := p_Pei_Information21;
v_INFO_Rec.Pei_Information22 := p_Pei_Information22;
v_INFO_Rec.Pei_Information23 := p_Pei_Information23;
v_INFO_Rec.Pei_Information24 := p_Pei_Information24;
v_INFO_Rec.Pei_Information25 := p_Pei_Information25;
v_INFO_Rec.Pei_Information26 := p_Pei_Information26;
v_INFO_Rec.Pei_Information27 := p_Pei_Information27;
v_INFO_Rec.Pei_Information28 := p_Pei_Information28;
v_INFO_Rec.Pei_Information29 := p_Pei_Information29;
v_INFO_Rec.Pei_Information30 := p_Pei_Information30;
CALL_EITS (v_INFO_Rec, 'AFTER', 'UPDATE');
RETURN;
END After_Update_Person_EIT;
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE After_Delete_Person_EIT (p_Person_Extra_Info_Id IN NUMBER,
p_Object_Version_Number IN NUMBER)
AS
v_INFO_Rec PER_PEOPLE_EXTRA_INFO%ROWTYPE;
BEGIN
/* Select *
into v_INFO_Rec
from PER_PEOPLE_EXTRA_INFO ppei
where ppei.person_extra_info_id = P_PERSON_EXTRA_INFO_ID;
CALL_EITS(v_INFO_Rec, 'AFTER', 'DELETE');
RETURN;*/
NULL;
END After_Delete_Person_EIT;
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE CALL_EITS (P_EIT_REC PER_PEOPLE_EXTRA_INFO%ROWTYPE,
P_EVENT_TIME VARCHAR,
P_EVENT_MODE VARCHAR2)
AS
BEGIN
-- IF P_EVENT_TIME = 'AFTER' THEN return; END IF;
IF P_EIT_REC.Pei_Information_Category = 'KSR_BANK_COMMITMENT'
THEN
EIT_BANK_COMMITMENT (P_EIT_REC, P_EVENT_TIME, P_EVENT_MODE);
END IF;
RETURN;
END CALL_EITS;
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE EIT_BANK_COMMITMENT (P_EIT_REC PER_PEOPLE_EXTRA_INFO%ROWTYPE,
P_EVENT_TIME VARCHAR,
P_EVENT_MODE VARCHAR2)
AS
v_msg VARCHAR2 (200);
v_Trans_Count NUMBER;
v_PASS_BANK_CODE VARCHAR2 (50) := 'SCSB';
BEGIN
---##################################
---user business case only
---##################################
END EIT_BANK_COMMITMENT;
END XX_EIT_PERSON_USERHOOKS;
/
----#################################
-- now step one is done and now we going to register it into the EBS
2- we
register it Standard User Hook all we will do is change
- ur packge , procudre name
- change the name of ur needed action in this userhook standard package
DECLARE
L_API_HOOK_ID NUMBER;
L_API_HOOK_CALL_ID NUMBER;
L_OBJECT_VERSION_NUMBER NUMBER;
L_SEQUENCE NUMBER;
L_API_MODULE_ID NUMBER;
L_OLD_PACKAGE_NAME HR_API_HOOK_CALLS.CALL_PACKAGE%TYPE
:= 'HR_PERSON_EXTRA_INFO_BK1';
-- return from first query
L_OLD_PROCEDURE_NAME HR_API_HOOK_CALLS.CALL_PROCEDURE%TYPE
:= 'CREATE_PERSON_EXTRA_INFO_B';
-- as return from first query
L_NEW_PACKAGE_NAME HR_API_HOOK_CALLS.CALL_PACKAGE%TYPE
:= 'XX_USERHOOKs';
--ur xxpkg
L_NEW_PROCEDURE_NAME HR_API_HOOK_CALLS.CALL_PROCEDURE%TYPE
:= 'xx_create_b';
--ur xx procudre of validation or
BEGIN
--Hr_Api_User_Hooks_Utility.Clear_Hook_Report;
BEGIN
SELECT API_HOOK_CALL_ID, OBJECT_VERSION_NUMBER
INTO L_API_HOOK_CALL_ID, L_OBJECT_VERSION_NUMBER
FROM HR_API_HOOK_CALLS
WHERE CALL_PACKAGE = L_NEW_PACKAGE_NAME
AND CALL_PROCEDURE = L_NEW_PROCEDURE_NAME
AND ENABLED_FLAG = 'Y';
DBMS_OUTPUT.PUT_LINE ('FOUND ');
-- Hr_Api_Hook_Call_Api.delete_api_hook_call (
-- p_validate => FALSE,
-- p_api_hook_call_id => L_API_HOOK_CALL_ID,
-- p_object_version_number => L_OBJECT_VERSION_NUMBER);
--
-- COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
SELECT HR_API_HOOKS_S.NEXTVAL INTO L_SEQUENCE FROM DUAL;
DBMS_OUTPUT.PUT_LINE ('L_SEQUENCE ' || L_SEQUENCE);
BEGIN
SELECT API_HOOK_ID
INTO L_API_HOOK_ID
FROM HR_API_HOOK_CALLS
WHERE CALL_PACKAGE = L_OLD_PACKAGE_NAME
AND CALL_PROCEDURE = L_OLD_PROCEDURE_NAME
AND ENABLED_FLAG = 'Y';
DBMS_OUTPUT.PUT_LINE ('HR_API_HOOK_CALLS');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
SELECT API_HOOK_ID
INTO L_API_HOOK_ID
FROM Hr_Api_Hooks
WHERE HOOK_PACKAGE = L_OLD_PACKAGE_NAME
AND HOOK_PROCEDURE = L_OLD_PROCEDURE_NAME;
DBMS_OUTPUT.PUT_LINE ('Hr_Api_Hooks');
END;
DBMS_OUTPUT.PUT_LINE ('L_API_HOOK_ID ' || L_API_HOOK_ID);
HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL (
P_VALIDATE => FALSE,
P_EFFECTIVE_DATE => SYSDATE,
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 => L_NEW_PACKAGE_NAME,
P_CALL_PROCEDURE => L_NEW_PROCEDURE_NAME,
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);
SELECT HAH.API_MODULE_ID
INTO L_API_MODULE_ID
FROM HR_API_HOOK_CALLS HAHC, HR_API_HOOKS HAH
WHERE HAHC.CALL_PACKAGE = L_NEW_PACKAGE_NAME
AND HAHC.CALL_PROCEDURE = L_NEW_PROCEDURE_NAME
AND HAHC.ENABLED_FLAG = 'Y'
AND HAHC.API_HOOK_ID = HAH.API_HOOK_ID;
DBMS_OUTPUT.PUT_LINE ('L_API_MODULE_ID ' || L_API_MODULE_ID);
HR_API_USER_HOOKS_UTILITY.CREATE_HOOKS_ONE_MODULE (L_API_MODULE_ID);
Hr_Api_User_Hooks_Utility.Write_One_Errors_Report (L_API_MODULE_ID);
END;
No comments:
Post a Comment