Scribd
Upload
844 views1 page

Pending Approval Leaves Query

This document contains an SQL query that selects data from multiple tables related to employee absences. The query retrieves data such as employee, absence, and transaction IDs, dates, statuses, and other metadata. It joins data from the HR_API_TRANSACTIONS table to absence records extracted from an XML document stored in that table. The results appear to contain consolidated employee absence information for reporting or other purposes.

Uploaded by

alhad7
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
844 views1 page

Pending Approval Leaves Query

This document contains an SQL query that selects data from multiple tables related to employee absences. The query retrieves data such as employee, absence, and transaction IDs, dates, statuses, and other metadata. It joins data from the HR_API_TRANSACTIONS table to absence records extracted from an XML document stored in that table. The results appear to contain consolidated employee absence information for reporting or other purposes.

Uploaded by

alhad7
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

select distinct

a1.transaction_ref_id,a1.BusinessGroupId,a1.PersonId,a1.AbsenceDays,a1.leave_start_
date,a1.leave_End_date,a1.CreatedBy,a1.CreationDate,
hat.TRANSACTION_ID,hat.CREATOR_PERSON_ID,hat.TRANSACTION_PRIVILEGE,
(select pap.full_name from PER_ALL_PEOPLE_F pap where pap.person_id =
hat.creator_person_id
AND trunc(SYSDATE) BETWEEN trunc(pap.effective_start_date) AND
trunc(pap.effective_end_date)) CREATERS_NAME,
hat.CREATED_BY,hat.CREATION_DATE,hat.LAST_UPDATE_DATE,hat.LAST_UPDATED_BY,hat.LAST_
UPDATE_LOGIN,hat.PRODUCT_CODE,
hat.STATUS,(select distinct meaning from fnd_lookup_values
where lookup_type='PQH_SS_TRANSACTION_STATUS'
and lookup_code= hat.STATUS
and language='US')status_meaning,
hat.FUNCTION_ID,hat.TRANSACTION_REF_TABLE,hat.TRANSACTION_REF_ID,hat.TRANSACTION_TY
PE,hat.ASSIGNMENT_ID,SELECTED_PERSON_ID,ITEM_TYPE,
hat.ITEM_KEY,hat.TRANSACTION_EFFECTIVE_DATE,hat.PROCESS_NAME,hat.TRANSACTION_IDENTI
FIER,hat.CREATOR_ROLE,hat.LAST_UPDATE_ROLE
from
(SELECT
extractvalue(VALUE(xx_row), '/PerAbsenceAttendancesEORow/AbsenceAttendanceId') AS
transaction_ref_id,
extractvalue(VALUE(xx_row), '/PerAbsenceAttendancesEORow/BusinessGroupId') AS
BusinessGroupId,
extractvalue(VALUE(xx_row), '/PerAbsenceAttendancesEORow/PersonId') AS PersonId,
extractvalue(VALUE(xx_row), '/PerAbsenceAttendancesEORow/AbsenceDays') AS
AbsenceDays,
to_date(extractvalue(VALUE(xx_row), '/PerAbsenceAttendancesEORow/DateStart'),'YYYY-
MM-DD') AS leave_start_date,
to_date(extractvalue(VALUE(xx_row), '/PerAbsenceAttendancesEORow/DateEnd'),'YYYY-
MM-DD') AS leave_End_date,
extractvalue(VALUE(xx_row), '/PerAbsenceAttendancesEORow/CreatedBy') AS CreatedBy,
extractvalue(VALUE(xx_row), '/PerAbsenceAttendancesEORow/CreationDate') AS
CreationDate
--to_date(extractvalue(VALUE(xx_row),
'/PerAbsenceAttendancesEORow/CreationDate'),'YYYY-MM-DD HH:MI:SS(24h)') AS
CreationDate
FROM HR_API_TRANSACTIONS xx_api,
TABLE(xmlsequence(extract(xmlparse(document transaction_document
wellformed),

'/Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow'))) xx_row
WHERE 1 = 1--xx_api.transaction_ref_id = 11511354
--and extractvalue(VALUE(xx_row), '/PerAbsenceAttendancesEORow/DateStart') is not
null
and extractvalue(VALUE(xx_row), '/PerAbsenceAttendancesEORow/AbsenceAttendanceId')
is not null) a1,HR_API_TRANSACTIONS hat
where a1.transaction_ref_id = hat.transaction_ref_id
and hat.status = 'Y'
and hat.TRANSACTION_IDENTIFIER = 'ABSENCES';

You might also like