Calling website or sending sms with parameters from oracle database 11g using pl/sql

DECLARE
  req   UTL_HTTP.REQ;
  resp  UTL_HTTP.RESP;
  value VARCHAR2(1024);  -- URL to post to
  v_url VARCHAR2(200) := 'http://yoursmsgateway.com/sendurlcomma.aspx?';
  -- Post Parameters
  v_param VARCHAR2(500);
  v_param_length NUMBER ;
CURSOR C1 IS

SELECT '97155xxxxx' Mobile,'Hello Ammar test from plsql' Text from dual
union all
SELECT '97155xxxxxx' Mobile,'Hello Afzal from plsql' Text from dual
; 

BEGIN
FOR I IN C1 LOOP
  -- Set up proxy servers if required
  --  UTL_HTTP.SET_PROXY('proxy.my-company.com', 'corp.my-company.com');
   v_param :=  'user=12345'||'&'||'pwd=12345'||'&'||'senderid=SMSAlert'||'&'||'mobileno='||I.MOBILE||',mobileno'||'&'||'msgtext='||I.TEXT||'&'||'priority=High'||'&'||'CountryCode=ALL';
   v_param_length :=  length(v_param);
  req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => 'POST');
  --  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
  UTL_HTTP.SET_HEADER (r      =>  req,
                       name   =>  'Content-Type',
                       value  =>  'application/x-www-form-urlencoded');
  UTL_HTTP.SET_HEADER (r      =>   req,
                       name   =>   'Content-Length',
                       value  =>   v_param_length);
  UTL_HTTP.WRITE_TEXT (r      =>   req,
                       data   =>   v_param); 
  resp := UTL_HTTP.GET_RESPONSE(req);
 
  dbms_output.put_line(i.mobile);
 
  --LOOP
    UTL_HTTP.READ_LINE(resp, value, TRUE);
    DBMS_OUTPUT.PUT_LINE(value);
  --END LOOP;
 
 
  UTL_HTTP.END_RESPONSE(resp);
  END LOOP; 
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(resp);
END;
/

Profiles in Oracle Apps R12

1. Useful Profiles
2. To Initialize Apps In toad:
3. To Initialize Apps in Oracle Report:
4. Secured Tables
5. To initialize in toad based on User_name, resp_name then
6. Arabic/English in toad/pl/sql (setting nls_lang)
7. How to View 'Diagnostics>Personalization' in Apps R12 forms
7.2. 'About this Page' in R12 OAF Pages.
7.3. How to Clear Cache in Apps R12??
https://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T457084.htm
http://appsr12help.blogspot.ae/2012/12/fndprofile-values.html



1.

select FND_PROFILE.VALUE('USER_ID') from dual
select FND_PROFILE.VALUE('RESP_ID') from dual
select FND_PROFILE.VALUE('RESP_APPL_ID') from dual
FND_PROFILE.value('PER_BUSINESS_GROUP_ID')
FND_PROFILE.value('PER_SECURITY_PROFILE_ID')

2.
To Initialize Apps In toad:
fnd_global.apps_initialize
  --(l_user_id,l_responsibility_id,l_resp_appl_id);
  (FND_PROFILE.VALUE('USER_ID'),
  FND_PROFILE.VALUE('RESP_ID'),
  FND_PROFILE.VALUE('RESP_APPL_ID'));
  mo_global.init ('S');

3.
To Initialize Apps in Oracle Report:
1. Before report trigger
    srw.USER_EXIT ('FND SRWINIT');
  fnd_global.apps_initialize
  --(l_user_id,l_responsibility_id,l_resp_appl_id);
  (FND_PROFILE.VALUE('USER_ID'),
  FND_PROFILE.VALUE('RESP_ID'),
  FND_PROFILE.VALUE('RESP_APPL_ID'));
  mo_global.init ('S');

2. After Report Trigger
SRW.USER_EXIT('FND SRWEXIT');

3. Add user parameter
P_CONC_REQUEST_ID  datatype - Number, Width - 200

4.
4. Check the query tables are secured or not (it should contain without _all_)
eg: per_people_f, per_assigments_f, pay_payrolls_f, hr_organization_units.


SELECT FND_PROFILE.value('PER_BUSINESS_GROUP_ID') BUSINESS_GROUP_ID,FND_PROFILE.value('PER_SECURITY_PROFILE_ID') PER_SEC_PROF_ID INTO
&P_BUSINESS_GROUP_ID, &P_PER_SEC_PROF_ID
FROM PER_ANALYSIS_CRITERIA C
WHERE C.ID_FLEX_NUM = 50317


5.
To initialize in toad based on User_name, resp_name then

DECLARE
  CURSOR c_details(p_user IN VARCHAR2, p_resp_name IN VARCHAR2) IS
    SELECT user_id,responsibility_id,responsibility_application_id, security_group_id
      FROM fnd_user_resp_groups
     WHERE user_id = (SELECT user_id FROM fnd_user WHERE user_name = p_user)
       AND responsibility_id in (SELECT responsibility_id FROM fnd_responsibility_tl WHERE responsibility_name = p_resp_name)
         ;
  CURSOR c_application(p_application_id IN NUMBER) IS
    SELECT application_short_name
      FROM fnd_application
     WHERE application_id = p_application_id
         ;
  l_user_id                NUMBER;
  l_responsibility_id      NUMBER;
  l_resp_appl_id           NUMBER;
  l_security_group_id      NUMBER;
  v_user_name              VARCHAR2(30);
  v_responsibility_name    VARCHAR(100);
  v_application_short_name VARCHAR2(10);
BEGIN
  v_user_name := :USER;
  v_responsibility_name := :RESPONSIBILITY_NAME;
  /**/
  OPEN c_details(v_user_name, v_responsibility_name);
  fetch c_details INTO l_user_id, l_responsibility_id, l_resp_appl_id, l_security_group_id;
  CLOSE c_details;
  /**/
  OPEN c_application(l_resp_appl_id);
  fetch c_application INTO v_application_short_name;
  close c_application;
  /**/
  fnd_global.apps_initialize(l_user_id,l_responsibility_id,l_resp_appl_id);
  mo_global.init ('S');
END;

6.
Arabic/English in toad/pl/sql develolper
alter session set NLS_LANGUAGE ='AMERICAN'--'ARABIC'
COMMIT
SELECT * from FUJ_MON_YEAR_V2
select userenv('lang') from dual


7. DO IT USER LEVEL:

7.1.System administrator Resp>profile
Query for : Utilities:%Diagnostics 
Change Value : Yes

7.1
System administrator Resp>profile
Query for : Personalize Self-Service Defn
Change Value : Yes

7.2
System administrator Resp>profile
Query for :FND: Personalization Region Link Enabled
Change Value : Yes

7.3.
Clearing Cache in Apps R12:
RESP>FUNCTIONAL ADMINISTRATOR
CORE SERVICES>CACHING FRAMEWORK>
GLOBAL CONFIGURATION>
CLICK , CLEAR ALL STATISTICS (YES)
CLICK , CLEAR ALL CACHE (YES)

SSHR Query for Pending/Approved txns in Apps R12

In SSHR eit/sit or leave is submitted then
entry will be in hr_api_transactions which have the status as 'Y'
and entry will be in PQH_SS_TRANSACTION_HISTORY for future history purposes with no status column.
 

--Approved transaction details

select count(selected_person_id)approved_txns,process_name,organization_id,organization_name, payroll_name
from(
select
--process_name
(select name from
PER_ABS_ATTENDANCE_TYPES_TL abs
where ABSENCE_ATTENDANCE_TYPE_ID = information5
and language = userenv('LANG')) process_name
,hrat.transaction_history_id, NVL (information1, information3) date_start, hrat.item_key,
NVL (information2, information4) date_end, information5 leave_type,
information8 DURATION, selected_person_id,
paaf.organization_id,
(select name from hr_all_organization_units haou
where haou.organization_id = paaf.organization_id)organization_name,
(select payroll_name from
pay_all_payrolls_f paf
where paf.payroll_id  = paaf.payroll_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date)Payroll_name
from
PQH_SS_TRANSACTION_HISTORY HraT,
PQH_SS_STEP_HISTORY hrst,
per_all_assignments_f paaf
            where selected_person_id = paaf.person_id
            and trunc(hrat.creation_date) between paaf.effective_start_date and paaf.effective_end_date
            and primary_flag = 'Y'
            and (paaf.payroll_id = :p_payroll_id OR (:p_payroll_id is null and paaf.payroll_id in (61,62,63,64,81)))
            and paaf.organization_id = nvl(:p_organization_id, paaf.organization_id)
            AND HraT.TRANSACTION_HISTORY_ID = HrST.TRANSACTION_HISTORY_ID
            AND PROCESS_NAME = 'HR_GENERIC_APPROVAL_PRC' --including only leaves
            AND TRUNC(hrat.CREATION_DATE) between :P_START_DATE AND :P_END_DATE
            AND hrat.TRANSACTION_HISTORY_ID NOT IN (
            SELECT TRANSACTION_ID FROM HR_API_TRANSACTIONS
            WHERE STATUS = 'Y')
)
group by process_name, organization_name, organization_id, payroll_name

--Pending transactions
select count(selected_person_id)pending_txns,process_name,organization_name, organization_id,Payroll_name
from(
SELECT distinct selected_person_id,paaf.organization_id
,(select name from
PER_ABS_ATTENDANCE_TYPES_TL abs
where ABSENCE_ATTENDANCE_TYPE_ID = information5
and language = userenv('LANG')) process_name
, hrat.transaction_id,hrat.item_key,
(select name from hr_all_organization_units haou
             where haou.organization_id = paaf.organization_id)organization_name,
(select payroll_name from
pay_all_payrolls_f paf
where paf.payroll_id  = paaf.payroll_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date)Payroll_name
FROM
HR_API_TRANSACTION_STEPS hrst,
HR_API_TRANSACTIONS hrat,
per_all_assignments_f paaf
            where selected_person_id = paaf.person_id
            and trunc(hrat.creation_date) between paaf.effective_start_date and paaf.effective_end_date
            and primary_flag = 'Y'
--            and (paaf.payroll_id = nvl(:p_payroll_id, paaf.payroll_id) OR (:p_payroll_id is null and paaf.payroll_id in (61,62,63,64,81)))
            and (paaf.payroll_id = :p_payroll_id OR (:p_payroll_id is null and paaf.payroll_id in (61,62,63,64,81)))
            and paaf.organization_id = nvl(:p_organization_id, paaf.organization_id)
            AND hrst.TRANSACTION_ID = hrat.TRANSACTION_ID
            AND hrat.STATUS = 'Y'
            AND PROCESS_NAME = 'HR_GENERIC_APPROVAL_PRC' --including only leaves
            AND TRUNC(hrat.CREATION_DATE) BETWEEN :P_START_DATE AND :P_END_DATE
            )
group by process_name, organization_name, organization_id, Payroll_name

Message in Master-detail form or Master Form asking to Save, even while Query the record in Apps R12 Forms 10g

This happens mainly due to post-query Trigger,
Add below line at the bottom of your post-query trigger, it will solve.

--the below will avoid showing message to save even while u query a existing record.
set_record_property(:system.trigger_record,:system.trigger_block,status,query_status);

API to upload data into FND_LOOKUP_VALUES in R12

DECLARE
   V_ERROR  VARCHAR2(4000);
   CURSOR get_lookup_details
   IS
  
      SELECT ltype.application_id,
             ltype.customization_level,
             ltype.creation_date,
             ltype.created_by,
             ltype.last_update_date,
             ltype.last_updated_by,
             ltype.last_update_login,
             tl.lookup_type,
             tl.security_group_id,
             tl.view_application_id,
             tl.description,
             tl.meaning
        FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype
       WHERE     ltype.lookup_type = 'FUJ_ARRIVAL_AIRPORT'
             AND ltype.lookup_type = tl.lookup_type
             AND language = 'US';

   CURSOR get_country
   IS
  
      SELECT distinct LOOKUP_CODE,MEANING , DESCRIPTION
      FROM XX_LOOKUP_VALUES
      --WHERE LOOKUP_CODE = '1'
     -- WHERE upload_status <> 'DONE'
      ;
     
   l_rowid   VARCHAR2 (100) := 0;
BEGIN
   FOR i IN get_lookup_details
   LOOP
      FOR j IN get_country
      LOOP
         l_rowid := NULL;
         BEGIN
            fnd_lookup_values_pkg.insert_row (
               x_rowid                 => l_rowid,
               x_lookup_type           => i.lookup_type,
               x_security_group_id     => i.security_group_id,
               x_view_application_id   => i.view_application_id,
               x_lookup_code           => j.LOOKUP_CODE,
               x_tag                   => '',
               x_attribute_category    => NULL,
               x_attribute1            => NULL,
               x_attribute2            => NULL,
               x_attribute3            => NULL,
               x_attribute4            => NULL,
               x_enabled_flag          => 'Y',
               x_start_date_active     =>NULL,
               x_end_date_active       => NULL,
               x_territory_code        => NULL,
               x_attribute5            => NULL,
               x_attribute6            => NULL,
               x_attribute7            => NULL,
               x_attribute8            => NULL,
               x_attribute9            => NULL,
               x_attribute10           => NULL,
               x_attribute11           => NULL,
               x_attribute12           => NULL,
               x_attribute13           => NULL,
               x_attribute14           => NULL,
               x_attribute15           => NULL,
               x_meaning               => j.MEANING,
               x_description           => j.DESCRIPTION,
               x_creation_date         => SYSDATE,
               x_created_by            => i.created_by,
               x_last_update_date      => i.last_update_date,
               x_last_updated_by       => i.last_updated_by,
               x_last_update_login     => i.last_update_login);

            COMMIT;

           update XX_LOOKUP_VALUES set upload_status= 'DONE' WHERE LOOKUP_CODE=J.LOOKUP_CODE;
         EXCEPTION
            WHEN OTHERS
            THEN
             V_ERROR := (SQLERRM);
                 update XX_LOOKUP_VALUES set upload_status= 'Inner Exception: ' || V_ERROR WHERE LOOKUP_CODE=J.LOOKUP_CODE;
         END;
      END LOOP;
   END LOOP;
--EXCEPTION
--   WHEN OTHERS
--   THEN
--      update XX_COUNTRY set upload_status='Main Exception: ' || V_ERROR  WHERE LOOKUP_CODE=J.LOOKUP_CODE;
END;


SELECT count(*) FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'FUJ_ARRIVAL_AIRPORT'
AND LANGUAGE = 'US'


-------------------------------custom table create------------------------------

--drop table XX_LOOKUP_VALUES

CREATE TABLE XX_LOOKUP_VALUES
(
  LOOKUP_CODE    VARCHAR2(50 BYTE),
  MEANING        VARCHAR2(200 BYTE),
  DESCRIPTION    VARCHAR2(200 BYTE),
  UPLOAD_STATUS  VARCHAR2(2000 BYTE)
)

Scheduling in linux to run the script every 2 minutes and 8 hours

step1:
creating new shell file script , and all commands to run in it

cat copy.sh

step2: editing the shell file to run the commands and  add #!/bin/sh in first line

vi copy.sh

#!/bin/sh
cp  <file from location to other location>
:wq!

step3 scheduling every 2 min and every 8 hours:

crowntab -e


*/2 * * * * <path of file to run eg: /tmp/copy.sh>
* */8 * * * <path of file to run eg: /tmp/copy.sh>

:wq!

Userhook for EIT in oracle apps R12

1. to Search userhook for EIT.

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_PERSON_EXTRA%'
AND ahm.api_module_type = 'BP'
AND ahk.api_hook_type = 'AP'
AND ahk.api_module_id = ahm.api_module_id

2759 API_HOOK_ID
1226 API_MODULE_ID

2. run the below script to add userhook.

DECLARE

L_API_HOOK_ID NUMBER := 2759 ;
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_SS_USERHOOK',  --package script below
                           p_call_procedure             => 'XXCREATE_PERSON_EXTRA_INFO_B', --poackage proecudre
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 ;

3.COMMIT

4.  check status with below query

SELECT *
FROM HR_API_HOOK_CALLS
WHERE api_hook_id = 2759;

STATUS = 'N'



5. run the pre-processor or below script to complete.

declare
l_api_module_id number := 1226; --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;


6. COMMIT

7. recheck the status should be 'V'

8.
to delete eit userhook:

begin
hr_api_hook_call_api.delete_api_hook_call
  (p_validate                           => FALSE,
   p_api_hook_call_id                   => 1249,
   p_object_version_number              => 2
  );
--
end;


script for package and procedure..................


CREATE OR REPLACE package APPS.XXFUJ_SS_USERHOOK is

  -- Author  : Ammar Galant
  -- Created : 8/8/2017
  -- Purpose : Self Service Userhooks

  procedure XXCREATE_PERSON_EXTRA_INFO_B(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);

end XXFUJ_SS_USERHOOK;



/************************************************* Package Body **********************************************************/


CREATE OR REPLACE PACKAGE BODY APPS.XXFUJ_SS_USERHOOK
IS
   PROCEDURE XXCREATE_PERSON_EXTRA_INFO_B (
      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)
   IS  
   
      LN_LEAVE_START DATE;     
        
   BEGIN
  
      IF P_INFORMATION_TYPE = 'FUJ_UPDATE_DELETE_LEAVE' THEN
        
         select date_start
          INTO LN_LEAVE_START
          from per_absence_attendances
          where ABSENCE_ATTENDANCE_ID = to_number(P_PEI_INFORMATION2);
      
         IF TRUNC(sysdate) >= LN_LEAVE_START AND TRUNC(SYSDATE) >= TO_DATE('11-AUG-2017')
         THEN
            hr_utility.set_message (800, 'FUJ_DELETE_LEAVE_VALID');
            hr_utility.raise_error;
         END IF;
      
       END IF;
  
   DBMS_OUTPUT.PUT_LINE('hi');
    
     END XXCREATE_PERSON_EXTRA_INFO_B;
END XXFUJ_SS_USERHOOK;
/