SQL query to extract data Pending approval transaction for employee transactions data

 select

txnd.transaction_id,

txnd.status,

txnd.state,

txnd.created_by,

txnd.creation_date,

txnd.last_update_date,

txnd.last_updated_by,

txnh.module_identifier,

txnh.subject,

txnh.subject_id,

txnh.object,

txnh.object_id,

txne.Fault_Date,

txne.Error_Details,

wft.TITLE,

wft.OUTCOME as WF_OUTCOME,

wft.STATE as WF_STATE,

Wft.Assignees,

wft.Assigneddate,

wft.Approvers,

wft.compositeinstanceid

from

Fusion.Hrc_Txn_Header Txnh, Fusion.Hrc_Txn_Data Txnd,

fa_fusion_soainfra.wftask wft,

fusion.hrc_txn_error txne

Where txnh.Transaction_Id = txnd.Transaction_Id

and txnh.transaction_id = txne.transaction_id(+)

And To_Char(Txnh.Transaction_Id) = Wft.Identificationkey(+)

and ((txnh.subject_id in (select distinct person_id from per_all_people_f

where person_number in ('1234')

))

or (txnh.subject_id in (select assignment_id from per_all_assignments_m

where assignment_type ='E' and person_id in

(select distinct person_id from per_all_people_f where person_number in

('1234')

)))

or (txnh.subject_id in (select period_of_service_id from

per_periods_of_service where person_id in

(select distinct person_id from per_all_people_f where person_number in

('1234')

))))

SQL query to get current approver and future or next approver for resignation transactions

 SELECT wt.ASSIGNEESDISPLAYNAME next_approver,--hrops_name

wt.FROMUSERDISPLAYNAME current_approver,

txnh.object_id assignment_id,

(

SELECT DISTINCT papf.person_number

FROM per_all_people_f papf,

per_all_assignments_f paaf

WHERE 1 = 1

AND papf.person_id = paaf.person_id

AND sysdate BETWEEN paaf.effective_start_date

AND paaf.effective_end_date

AND paaf.assignment_id = txnh.object_id

) person_number,

(

SELECT to_date(EXTRACTVALUE(xmltype.createxml(DATA_CACHE), '/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/NotifiedTerminationDate/DATA'), 'rrrr-mm-dd')

FROM hrc_txn_data txnd

WHERE 1 = 1

AND LENGTHB(TO_CHAR(SUBSTR(DATA_CACHE, 1, 4000))) <> 0

AND txnd.transaction_id = wt.identificationkey

) NTD,

(

SELECT to_date(EXTRACTVALUE(xmltype.createxml(DATA_CACHE), '/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/ActualTerminationDate/DATA'), 'rrrr-mm-dd')

FROM hrc_txn_data txnd

WHERE 1 = 1

AND LENGTHB(TO_CHAR(SUBSTR(DATA_CACHE, 1, 4000))) <> 0

AND txnd.transaction_id = wt.identificationkey

) ATD,

(

SELECT DISTINCT ppnf.display_name

FROM per_person_names_f ppnf,

per_all_people_f papf,

per_all_assignments_f paaf

WHERE 1 = 1

AND ppnf.person_id = papf.person_id

AND sysdate BETWEEN ppnf.effective_start_date

AND ppnf.effective_end_date

AND ppnf.NAME_TYPE = 'GLOBAL'

AND papf.person_id = paaf.person_id

AND sysdate BETWEEN paaf.effective_start_date

AND paaf.effective_end_date

AND paaf.assignment_id = txnh.object_id

) DISPLAY_NAME,

trunc(CREATEDDATE) trx_date

FROM fa_fusion_soainfra.wftask wt,

hrc_txn_header txnh

WHERE 1 = 1

AND wt.identificationkey = txnh.transaction_id

AND ((ASSIGNMENTCONTEXT LIKE '%Above%'

AND wt.PUSHBACKSEQUENCE <> 'INITIAL_ASSIGNEES;1')

OR (ASSIGNMENTCONTEXT LIKE '%Below%'))

and wt.TASKDEFINITIONNAME = 'ResignationApproval'

AND Txnh.OBJECT = 'PER_ALL_ASSIGNMENTS_M'

AND Txnh.module_identifier IN ('Resignation','Terminations')

AND wt.COMPONENTNAME IN ('ResignationApproval','TerminationsApproval')

AND trunc(wt.UPDATEDDATE) = trunc(sysdate)

AND wt.ENDDATE IS NULL

SQL query to extract from data_cache field on HRC_TRX_DATA with transaction status

 

SELECT

person.person_number,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/Department')

Department_old,

EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/asgCalcAttributeHash/Department')

Department_new,

INITCAP (names.display_name) display_name,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit')

BusinessUnit_old,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/asgCalcAttributeHash/BusinessUnit')

BusinessUnit_new,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/Location')

Location_old,

EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/asgCalcAttributeHash/Location')

Location_new,

'InProgress' STATUS,

(SELECT per_all_people_f.person_number||'-'||INITCAP (per_person_names_f.display_name)

FROM PER_ASSIGNMENT_SUPERVISORS_F,

per_person_names_f,

per_all_people_f

WHERE PER_ASSIGNMENT_SUPERVISORS_F.manager_id =

per_person_names_f.person_id

AND per_person_names_f.person_id =

per_all_people_f.person_id

AND PER_ASSIGNMENT_SUPERVISORS_F.MANAGER_TYPE =

'LINE_MANAGER'

AND TRUNC (SYSDATE) BETWEEN TRUNC (

PER_ASSIGNMENT_SUPERVISORS_F.EFFECTIVE_START_DATE)

AND TRUNC (

PER_ASSIGNMENT_SUPERVISORS_F.EFFECTIVE_END_DATE)

AND TRUNC (SYSDATE) BETWEEN TRUNC (

per_person_names_f.EFFECTIVE_START_DATE)

AND TRUNC (

per_person_names_f.EFFECTIVE_END_DATE)

AND TRUNC (SYSDATE) BETWEEN TRUNC (

per_all_people_f.EFFECTIVE_START_DATE)

AND TRUNC (

per_all_people_f.EFFECTIVE_END_DATE)

and PER_ASSIGNMENT_SUPERVISORS_F.assignment_id=asg.assignment_id

AND per_person_names_f.name_type = 'GLOBAL'

)curr_reporting,

/* EXTRACTVALUE (xmltype ('' || replace(txnD.data_cache,',','') || ''),

'TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/CEO/EO/EmployeeTermsDEORow/CEO/EO/EmployeeAssignmentDEORow/CEO/EO/AssignmentSupervisorDEORow/ManagerName/DATA[1]')

*/null prapose_mgr,

asg.effective_start_date doj,

EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/basicInfoAttributes/ReasonName')REASON

FROM hrc_txn_header txnH,

per_all_people_f person,

per_all_assignments_m asg,

per_person_names_f names,

hrc_txn_data txnD,

FA_FUSION_SOAINFRA.WFTASK wft,

per_persons ppos

WHERE UPPER (txnH.Object) IN ('PER_ALL_ASSIGNMENTS_M')

AND txnH.object_id = asg.assignment_id

AND SYSDATE BETWEEN asg.effective_start_date

AND asg.effective_end_date

AND asg.person_id = person.person_id

AND SYSDATE BETWEEN person.effective_start_date

AND person.effective_end_date

AND names.person_id = person.person_id

AND person.person_id = ppos.person_id

AND TO_CHAR (txnh.transaction_id) = wft.identificationkey(+)

AND SYSDATE BETWEEN names.effective_start_date

AND names.effective_end_date

AND names.name_type = 'GLOBAL'

AND txnH.transaction_id = txnD.transaction_id

AND txnD.status = 'PENDING'

AND txnH.MODULE_IDENTIFIER = 'Transfers'

AND person.person_number =nvl(:L_PERSON_NUM,person.person_number)

and (EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit')) =nvl(:L_BU_NAME,(EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit')))

and wft.ASSIGNEESDISPLAYNAME is not null

SQL query to search for Pending Transaction of Employee Assignments changes

 

SELECT
person.person_number||'-'||INITCAP (names.display_name) pp,person.person_number
FROM hrc_txn_header txnH,
per_all_people_f person,
per_all_assignments_m asg,
per_person_names_f names,
hrc_txn_data txnD,
FA_FUSION_SOAINFRA.WFTASK wft,
per_persons ppos
WHERE UPPER (txnH.Object) IN ('PER_ALL_ASSIGNMENTS_M')
AND txnH.object_id = asg.assignment_id
AND SYSDATE BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.person_id = person.person_id
AND SYSDATE BETWEEN person.effective_start_date
AND person.effective_end_date
AND names.person_id = person.person_id
AND person.person_id = ppos.person_id
AND TO_CHAR (txnh.transaction_id) = wft.identificationkey(+)
AND SYSDATE BETWEEN names.effective_start_date
AND names.effective_end_date
AND names.name_type = 'GLOBAL'
AND txnH.transaction_id = txnD.transaction_id
AND txnD.status = 'PENDING'
AND txnH.MODULE_IDENTIFIER = 'Transfers'
and wft.ASSIGNEESDISPLAYNAME is not null