Self Service Related SQL Queries

By | January 4, 2015

Self Service Related SQL Queries

In this article of Oracle HRMS Overview, we are going to share SQL queries which are very much helpful while support self service module.

Approval History for a self service Transaction

Notification Details

 

Approval History for a Self Service Transaction

On the Basis of Notification ID

This query shows approval history for a self service transaction on the basis of notification id.

SELECT   ppf.full_name,ph.*
FROM      pqh_ss_approval_history ph, fnd_user fu, per_all_people_f ppf
WHERE  1=1
AND          fu.user_name = ph.user_name
AND          fu.employee_id = ppf.person_id
AND          TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
AND          transaction_item_key = (SELECT item_key FROM wf_notifications wf WHERE notification_id IN (7844457))
ORDER BY  ph.creation_date desc

Query Sample Results

SSHR Approval History

Back to top

On the Basis of Item Key

This query shows approval history for a self service transaction on the basis of Item Key.

SELECT   ppf.full_name,ph.*
FROM      pqh_ss_approval_history ph, fnd_user fu, per_all_people_f ppf
WHERE  1=1
AND         fu.user_name = ph.user_name
AND         fu.employee_id = ppf.person_id
AND         TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
AND         transaction_item_key =’143967′
ORDER BY  ph.creation_date desc

Query Sample Results (Same Result as Above)

SSHR Approval History Based on Notification ID

Back to top

 Notification Details

On the Basis of Notification ID

On every self service notification, Notification ID is mentioned in the header section. From this notification id, we can start querying the details for a self service transaction.

SELECT      item_key,ppf.full_name,wf.group_id,wf.*
FROM         wf_notifications wf, per_all_people_f ppf
WHERE     1=1
AND            notification_id in ( 7844457)
AND            recipient_role = ppf.employee_number
AND            trunc(sysdate) between effective_start_date and effective_end_date
ORDER BY  begin_date desc

Query Sample Results

SSHR Notification Detail

Back to top

On the Basis of Group ID

If same notification is sent to multiple users at same time, Oracle generates same group_id with different notification id for each row. Example of such cases are PARALLEL APPROVER notifications, and FOR-YOUR-INFORMATION notifications. For such cases, we can get the group_id from above query and use that group_id in this query.

SELECT      item_key,ppf.full_name,wf.group_id,wf.*
FROM         wf_notifications wf, per_all_people_f ppf
WHERE     1=1
AND            group_id in ( 3972512)
AND            recipient_role = ppf.employee_number
AND            trunc(sysdate) between effective_start_date and effective_end_date
ORDER BY  begin_date desc

Query Sample Results

Notification Detail

Back to top

On the Basis of Item Key

If we wan to see all notifications for any self service transaction. For such case, Item Key should be used in the where clause. We can get item key on the basis of notification_id as shown above and use that item_key value in this query.

SELECT      item_key,ppf.full_name,wf.group_id,wf.*
FROM         wf_notifications wf, per_all_people_f ppf
WHERE     1=1
AND            item_key in ( ‘1836’)
AND            recipient_role = ppf.employee_number
AND            trunc(sysdate) between effective_start_date and effective_end_date
ORDER BY  begin_date desc

Sample Data

Notification Detail

Work in Progress Transactions in SSHR

Following Query gives you the transactions which are in progress and waiting for final action

select ht.process_name, ht.CREATION_DATE,ht.CREATOR_PERSON_ID,ht.ITEM_KEY,ppf.employee_number,ppf.full_name,pbg.name
, hr_general.decode_location(paaf.location_id) “Location”
, hr_general.decode_organization(paaf.organization_id) “Organization Name”
, substr(hr_general.decode_people_group(paaf.people_group_id),5,2) “Department”
, pap.payroll_name “Payroll”
from hr_api_transactions ht, per_all_people_f PPF,per_business_groups pbg,per_all_assignments_f paaf,pay_all_payrolls_f pap
where (trunc(ht.creation_date) = trunc(sysdate-1) or trunc(ht.creation_date) = trunc(sysdate))
and ht.item_key is not null
AND ht.CREATOR_PERSON_ID = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and ppf.person_id = paaf.person_id
and ppf.business_group_id = pbg.business_group_id
and pbg.business_group_id = 83
and paaf.payroll_id = pap.payroll_id

SSHR Transactions History

select ht.process_name, ht.CREATION_DATE,ht.CREATOR_PERSON_ID,ht.ITEM_KEY,ppf.employee_number,ppf.full_name,pbg.name
, hr_general.decode_location(paaf.location_id) “Location”
, hr_general.decode_organization(paaf.organization_id) “Organization Name”
, substr(hr_general.decode_people_group(paaf.people_group_id),5,2) “Department”
, pap.payroll_name “Payroll”
from PQH_SS_TRANSACTION_HISTORY ht, per_all_people_f PPF,per_business_groups pbg,per_all_assignments_f paaf,pay_all_payrolls_f pap
where (trunc(ht.creation_date) = trunc(sysdate-1) or trunc(ht.creation_date) = trunc(sysdate))
and ht.item_key is not null
AND ht.CREATOR_PERSON_ID = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and ppf.person_id = paaf.person_id
and ppf.business_group_id = pbg.business_group_id
and pbg.business_group_id = 83
and paaf.payroll_id = pap.payroll_id

Note: HR_API_TRANSACTIONS table contains all active transactions. As soon as transaction is finally approved or rejected, Data is purged from this table.
PQH_SS_TRANSACTION_HISTORY tables contains all transactions. It is a history tables. Transactions in it are never deleted.

Back to top

In this post i explained the approval history for a transaction. Through these tables, we shall get the users who already have taken action for a SSHR transaction. But what if we need the approval chain for any transaction which is not completed yet. We shall need to user an HRMS API provided for this reason. Please read my article SSHR Transaction Approval List to get insight on this API.

Other Posts

OLM Tables Hierarchy
Free Online Oracle Apps Tests
HRMS Interview Questions
Apps Technical Questions
Fast Formula in Oracle HRMS