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