Oracle Apps R12 HRMS Update API Position Status = 'Invalid'

---single position
declare
l_effective_date           date := sysdate ;
l_position_id              per_positions.position_id%type := 551441;
l_status                   varchar2(40) := 'INVALID';
l_eff_start_date           date;
l_eff_end_date             date;
l_validate_mode            boolean := false;
l_object_version_number    hr_all_positions_f.object_version_number%type;
l_position_definition_id   number;
l_pos_name                 per_positions.name%type;
l_pos_title_code           fnd_lookup_values.lookup_code%type;
l_datetrack_mode VARCHAR2(20);
l_valid_grades_changed_warning BOOLEAN;
--
begin
   ---   
   l_datetrack_mode         := 'UPDATE';
   l_position_definition_id := NULL;
   l_eff_start_date         := NULL;
   l_eff_end_date           := NULL;
   l_pos_name               := NULL;
   --l_effective_date         := trunc(sysdate);
   --l_pos_number             := '80304589.';
   --l_pos_title_code         := 'MECH_TECH';
   --l_object_version_number  := 3;

SELECT OBJECT_VERSION_NUMBER INTO l_object_version_number
FROM HR_POSITIONS_F
WHERE POSITION_ID = L_POSITION_ID
AND l_effective_date  BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;

   --
   hr_position_api.update_position
                 ( p_validate                       => l_validate_mode
                  ,p_position_id                  => l_position_id
                  ,p_effective_start_date     => l_eff_start_date
                  ,p_effective_end_date      => l_eff_end_date
                  ,p_position_definition_id   => l_position_definition_id
                  ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
                  ,p_status                          => l_status       
                  ,p_name                          => l_pos_name
                  --,p_segment1                    => l_segment1
                  --,p_segment2                    => l_pos_title_code                     
                  ,p_object_version_number              => l_object_version_number
                  ,p_effective_date             => l_effective_date
                  ,p_datetrack_mode         => l_datetrack_mode
                 );
                      
     dbms_output.put_line('Update Successful : ');
     --Important: regenerate_position_name needs to be called when there is a name update
     --                     
     apps.hr_position_api.regenerate_position_name(l_position_id);
     --                 
     dbms_output.put_line('Regenerate Successful : ');
                    
   if l_object_version_number is null then
      dbms_output.put_line('hr_position_api.update_position API Error: '||sqlerrm);
      rollback;
   elsif l_valid_grades_changed_warning then
      dbms_output.put_line('Warning: Grade changed');
      rollback;   
   else
      commit;
   end if; 
--
exception
  when others then
     dbms_output.put_line('hr_position_api.update_position API failed with error :'||sqlerrm);
     rollback;
end;          


--with cursor

declare
l_effective_date           date := sysdate ;
--l_position_id              per_positions.position_id%type := 3649;
l_status                   varchar2(40) := 'INVALID';
l_eff_start_date           date;
l_eff_end_date             date;
l_validate_mode            boolean := false;
l_object_version_number    hr_all_positions_f.object_version_number%type;
l_position_definition_id   number;
l_pos_name                 per_positions.name%type;
l_pos_title_code           fnd_lookup_values.lookup_code%type;
l_datetrack_mode VARCHAR2(20);
l_valid_grades_changed_warning BOOLEAN;

CURSOR C1 IS

select POSITION_ID , OBJECT_VERSION_NUMBER , NAME, STATUS
from HR_POSITIONS_F
where ORGANIZATION_ID in (101112,144121)
;
--
begin
   ---
 
   FOR I IN C1 LOOP 
   l_datetrack_mode         := 'UPDATE';
   l_position_definition_id := NULL;
   l_eff_start_date         := NULL;
   l_eff_end_date           := NULL;
   l_pos_name               := NULL;
 


/*SELECT OBJECT_VERSION_NUMBER, NAME INTO l_object_version_number, l_pos_name
FROM HR_POSITIONS_F
WHERE POSITION_ID = I.POSITION_ID
AND l_effective_date  BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;
*/
   --
   hr_position_api.update_position
                 ( p_validate                       => l_validate_mode
                  ,p_position_id                  => I.POSITION_ID--l_position_id
                  ,p_effective_start_date     => l_eff_start_date
                  ,p_effective_end_date      => l_eff_end_date
                  ,p_position_definition_id   => l_position_definition_id
                  ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
                  ,p_status                          => l_status     
                  ,p_name                          => I.NAME
                  --,p_segment1                    => l_segment1
                  --,p_segment2                    => l_pos_title_code                   
                  ,p_object_version_number              => I.OBJECT_VERSION_NUMBER--l_object_version_number
                  ,p_effective_date             => l_effective_date
                  ,p_datetrack_mode         => l_datetrack_mode
                 );
                     
     dbms_output.put_line('Update Successful : '||I.POSITION_ID);
     --Important: regenerate_position_name needs to be called when there is a name update
     --                   
     apps.hr_position_api.regenerate_position_name( I.POSITION_ID);--l_position_id);
     --               
     dbms_output.put_line('Regenerate Successful : '||I.POSITION_ID);
               
   if I.OBJECT_VERSION_NUMBER is null then
      dbms_output.put_line('hr_position_api.update_position API Error: '||sqlerrm||' '||I.POSITION_ID);
      rollback;
   elsif l_valid_grades_changed_warning then
      dbms_output.put_line('Warning: Grade changed'||I.POSITION_ID);
      rollback; 
   else
      commit;
   end if;
--
 END LOOP; 
exception
  when others then
     dbms_output.put_line('hr_position_api.update_position API failed with error :'||sqlerrm);
     rollback;
     
end;          

Oracle Apps R12 HRMS Create Position

--With Cursor

DECLARE

   L_BUSINESS_GROUP_ID            NUMBER := 81;

   /* Local Variables */

   L_POSITION_NAME                VARCHAR2 (100);

   L_POSITION_ID                  NUMBER;

   L_STATUS                       VARCHAR2 (5) := 'True';

   L_JOB_ID                       PER_JOBS.JOB_ID%TYPE;

   L_SEGMENT2                     VARCHAR2 (200);

   L_ORGANIZATION_ID              HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE;

   L_ERROR_MESSAGE                VARCHAR2 (1000);

   L_VALIDATE_CNT                 NUMBER;

   /* Out Variables */

   L_EFFECTIVE_START_DATE         DATE;

   L_EFFECTIVE_END_DATE           DATE;

   OUT_P_POSITION_ID              NUMBER;

   OUT_P_OBJECT_VERSION_NUMBER    NUMBER;

   OUT_P_POSITION_DEFINITION_ID   NUMBER;

   OUT_P_NAME                     VARCHAR2 (250);

   L_TOTAL_RECORDS                NUMBER := 0;

   L_SUCCESS_RECORDS              NUMBER := 0;

   L_FAILURE_RECORDS              NUMBER := 0;

   CURSOR C1

   IS

        

        SELECT ROWID ROWIDS,

               TRIM (POSITION_NAME) POSITION_NAME,SEGMENT3,

               (SELECT PJ.JOB_ID

                  FROM PER_JOBS PJ

                 WHERE 1 = 1 AND UPPER(PJ.NAME) = UPPER(TRIM (A.JOB_NAME))

                 AND BUSINESS_GROUP_ID =81

                 AND ROWNUM =1

                 )

                  JOB_ID,

               JOB_NAME,

               (SELECT ORGANIZATION_ID

                  FROM HR_ORGANIZATION_UNITS

                 WHERE 1 = 1 AND UPPER (NAME) = UPPER (TRIM (A.DEPARTMENT_NAME))

                 AND ROWNUM =1

                 )

                  ORG_ID,

               DEPARTMENT_NAME

          FROM TESTA A

         WHERE 1 = 1

               AND NOT EXISTS

                          (SELECT *

                             FROM PER_POSITIONS PP,

                                  PER_POSITION_DEFINITIONS PPD

                            WHERE 1 = 1

                                  AND PP.POSITION_DEFINITION_ID =

                                         PPD.POSITION_DEFINITION_ID

                                  AND UPPER(NAME) = UPPER(TRIM( (A.POSITION_NAME))))

--                                  AND TRIM(UPPER(POSITION_NAME)) NOT IN ('QUALITY MANAGER',UPPER('A/C Technician'),'EMERGENCY MEDIC TECHNICIAN', UPPER('EXECUTIVE SECRETARY'), UPPER('FOREMAN'),

--                                  UPPER('GROUND EQPT OPERATOR') , UPPER('PAX SERVICES/AGENT'))

--                                  AND 'FIA.'||UPPER(POSITION_NAME)||'.'||SEGMENT3 NOT IN ('FIA.SECRETARY.3')

                                  AND STATUS IS  NULL --OR CHECK ANY DUPLICATES POSITIONS EVEN WITH DIFF ORGS WILL THROW ERROR

--                                  AND UPPER(POSITION_NAME) <> UPPER('DRIVER')

      ORDER BY POSITION_NAME;

      

      

BEGIN

   fnd_global.apps_initialize (0, 50419, 800);        -- 0 sysadmin user id

   FOR C1_REC1 IN C1

   LOOP

   DBMS_OUTPUT.PUT_LINE(C1_REC1.POSITION_NAME||'.'||C1_REC1.SEGMENT3);

   BEGIN

      OUT_P_POSITION_ID := NULL;

      OUT_P_OBJECT_VERSION_NUMBER := NULL;

      OUT_P_POSITION_DEFINITION_ID := NULL;

      OUT_P_NAME := NULL;

      L_EFFECTIVE_START_DATE := NULL;

      L_EFFECTIVE_END_DATE := NULL;

      HR_POSITION_API.CREATE_POSITION (

         P_EFFECTIVE_DATE           => TO_DATE('01-JAN-1950'),

         P_JOB_ID                   => C1_REC1.JOB_ID,

         P_ORGANIZATION_ID          => C1_REC1.ORG_ID,

         P_DATE_EFFECTIVE           => TO_DATE('01-JAN-1950'),

         P_SEGMENT1                 => 'FIA', --,p_segment2 => c1_rec1.job_title

         P_SEGMENT2                 => Initcap(C1_REC1.POSITION_NAME), 

         P_SEGMENT3                 => C1_REC1.SEGMENT3,                                         

         P_FTE                      => NULL,

         P_MAX_PERSONS              => NULL,

         P_POSITION_ID              => OUT_P_POSITION_ID,

         P_OBJECT_VERSION_NUMBER    => OUT_P_OBJECT_VERSION_NUMBER,

         P_POSITION_DEFINITION_ID   => OUT_P_POSITION_DEFINITION_ID,

         P_NAME                     => OUT_P_NAME,

         P_EFFECTIVE_START_DATE     => L_EFFECTIVE_START_DATE,

         P_EFFECTIVE_END_DATE       => L_EFFECTIVE_END_DATE);

      UPDATE TESTA

         SET STATUS = 'SUCCESS'

       WHERE ROWID = C1_REC1.ROWIDS;

      COMMIT;

      DBMS_OUTPUT.PUT_LINE (

         '#############################################################');

      DBMS_OUTPUT.PUT_LINE (' out_p_position_id : ' || OUT_P_POSITION_ID);

      DBMS_OUTPUT.PUT_LINE ('out_p_name : ' || OUT_P_NAME);

      DBMS_OUTPUT.PUT_LINE (

         ' l_effective_start_date: ' || L_EFFECTIVE_START_DATE);

      DBMS_OUTPUT.PUT_LINE (

         '#############################################################');

  exception when others then

                                dbms_output.put_line(sqlerrm);

                                end;     

                      end loop;

   EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

declare
--need to pass parameters
l_business_group_id        number := 81;        
l_job_id                   per_positions.job_id%type := 5198; --job_id
l_pos_org_id               per_positions.position_id%type := 108; --org_id
l_segment1                 varchar2(20) := 'ح ك'; --our segment1 captures valueset code of organizations
l_segment2                 varchar2(20) := '12345'; --our segment2 captures name of position
l_segment3                 varchar2(20) := '1';        -- segment3 i guess constant 1
l_effective_date           date := sysdate;            --effective date
-----------------------------------------------------
l_eff_start_date           date;
l_eff_end_date             date;
l_date_end                 date := hr_general.end_of_time;
l_validate_mode            boolean := false;
l_pos_type                 fnd_lookup_values.lookup_code%type;
l_avail_id                 per_shared_types.shared_type_id%type;
l_fte                      number;
l_max_persons              number;
l_probation_period         number;
l_probation_unit_desc      varchar2(500);
l_orcl_pos_title_code      fnd_lookup_values.lookup_code%type;
l_attribute8               hr_all_positions_f.attribute8%type;
l_attribute9               hr_all_positions_f.attribute9%type;
l_grade_id                 hr_all_positions_f.entry_grade_id%type;
l_barg_unit_cd             hr_all_positions_f.bargaining_unit_cd%type;
l_attribute6               hr_all_positions_f.attribute6%type;
l_working_hours            hr_all_positions_f.working_hours%type;
l_frequency                hr_all_positions_f.frequency%type;
l_position_id              per_positions.position_id%type;
l_object_version_number    hr_all_positions_f.object_version_number%type;
l_position_definition_id   number;
l_pos_name                 per_positions.name%type;
--
begin
   ---   
    --l_pos_org_id               := 108;
--    l_effective_date           := trunc(sysdate);
    l_date_end                 := hr_general.end_of_time;
    --l_job_id                   := 5198;
    l_pos_type                 := 'SHARED';
    l_avail_id                 := 1;
    l_fte                      := 1;
    l_max_persons              := 100;
    l_probation_period         := 6;
    l_probation_unit_desc      := 'M';
    l_frequency                := 'W';
    l_working_hours            := 37; 
    l_grade_id                 := 77;
    l_barg_unit_cd             := 'EWP';
    l_attribute6               := NULL;
    l_orcl_pos_title_code      := 'MECH_TECH';
    --
    l_position_id              := NULL;
    l_object_version_number    := NULL;
    l_eff_start_date           := NULL;
    l_eff_end_date             := NULL;
    l_position_definition_id   := NULL;
    l_pos_name                 := NULL;

    --
    hr_position_api.create_position
            (
              p_job_id                      => l_job_id             --The job for the position
             ,p_organization_id             => l_pos_org_id
             ,p_effective_date              => l_effective_date 
             ,p_date_effective              => l_effective_date     --The date on which the position becomes active
             --,p_date_end                    => l_date_end
             ,p_validate                    => l_validate_mode
             ,p_position_type               => l_pos_type
             --,p_availability_status_id      => l_avail_id
             ,p_fte                         => l_fte
             ,p_max_persons                 => l_max_persons
             --,p_probation_period            => l_probation_period
             --,p_probation_period_unit_cd    => l_probation_unit_desc
             ,p_business_group_id           => l_business_group_id
             ,p_segment1                    => l_segment1
             ,p_segment2                    => l_segment2
             ,p_segment3                    => l_segment3
             --,p_segment2                    => l_orcl_pos_title_code
             --,p_attribute8                  => l_attribute8
             --,p_attribute9                  => l_attribute9
             --,p_entry_grade_id              => l_grade_id
             --,p_bargaining_unit_cd          => l_barg_unit_cd
             --,p_attribute6                  => l_attribute6
             --
             --,p_working_hours               => l_working_hours
             --,p_frequency                   => l_frequency
             --OUT
             ,p_position_id                 => l_position_id
             ,p_object_version_number       => l_object_version_number
             ,p_effective_start_date        => l_eff_start_date
             ,p_effective_end_date          => l_eff_end_date
             --IN/OUT                     
             ,p_position_definition_id      => l_position_definition_id
             ,p_name                        => l_pos_name
           );
                    
   if l_position_id is null or l_object_version_number is null then
      dbms_output.put_line('hr_position_api.create_position API Error: '||sqlerrm);
      rollback;
   else
      commit;
   end if; 
--
exception
  when others then
     dbms_output.put_line('hr_position_api.create_position API failed with error :'||sqlerrm);
     rollback;
end;  

Oracle forms 10g to restrict the Query in the Form based on column value

DECLARE
  v_def_where   VARCHAR2(2000) := Get_Block_Property('XXFUJ_PM_DETAILS',DEFAULT_WHERE);
  v_new_where   VARCHAR2(2000);
  --v_condition varchar2(100) := 'EMPLOYEE';
 
BEGIN
  v_new_where := 'CATEGORY = ''MANAGER''';
  Set_Block_Property('XXFUJ_PM_DETAILS',DEFAULT_WHERE, v_new_where);
  Execute_Query;
  -- Now reset the DEFAULT_WHERE back to it's original value.
  --Set_Block_Property('TRANS',DEFAULT_WHERE, v_def_where);
END;

---------------

CATEGORY = ''MANAGER''            --two single quotes

<column_name> = ''<TEXT>''

Converting XML report of excel output format text columns into numbers

Cause: Space at the end of the number fields.
Action:
Exporting data of number format as text, and spaces are suffixed, to remove it.

=LEFT(A1,LEN(A1)-1)
COPY THE VALUE IN TO THE OTHER COLUMN AS VALUES
THEN IT WIL POPUP TO CONVERT AS NUMBER, CLICK IT.

ITS DONE.

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