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