Sample SQL Query (You can modify as required):
-----------------------------------
txnc.status_category,
txnc.created_by,
txnc.object_name,
txnc.process_category,
txnc.change_effective_date,
txnd.status,
extractValue(xmltype('<root>'||txnd.data_cache||'<root>'),'root/TRANSACTION/TransCtx/PersonId')
as person_id,
extractValue(xmltype('<root>'||txnd.data_cache||'<root>'),'root/TRANSACTION/TransCtx/AssignmentId') as assignment_id,
extractValue(xmltype('<root>'||txnd.data_cache||'<root>'),'root/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/CEO/EO/EmployeeAssignmentDEORow/DEEventData/EventDEOData/EventEOAttrData/PersonId/newValue') as transactionpersonid,
extractValue(xmltype('<root>'||txnd.data_cache||'<root>'),'root/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/CEO/EO/EmployeeAssignmentDEORow/DEEventData/EventDEOData/EventEOAttrData/AssignmentId/newValue') as transactionassignmentid,
extractValue(xmltype('<root>'||txnd.data_cache||'<root>'),'root/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/CEO/EO/EmployeeAssignmentDEORow/DEEventData/EventDEOData/EventEOAttrData/BusinessUnitId/newValue') as transactionbusinessunitid,
extractValue(xmltype('<root>'||txnd.data_cache||'<root>'),'root/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/CEO/EO/EmployeeAssignmentDEORow/DEEventData/EventDEOData/EventEOAttrData/LocationId/newValue') as transactionlocationid,
txnd.last_update_date
from
fusion.hrc_txn_console_entry txnc,
fusion.hrc_txn_header txnh,
fusion.hrc_txn_data txnd,
fa_fusion_soainfra.wftask wft
where txnh.object in ('PER_ALL_ASSIGNMENTS_M','PER_ALL_PEOPLE_F')
and txnh.transaction_id = txnd.transaction_id
and txnd.transaction_id = txnc.transaction_id
and txnc.status_category in (:status_category)
and wtf.identificationkey = to_char(txnh.transaction_id)
and to_date(to_char(txnd.last_update_date,'mm/dd/yyyy hh24:mi:ss'),'mm/dd/yyyy hh24:mi:ss')
between to_date(to_char((decode(nvl(to_char(:start_date,'mm/dd/yyyy hh24:mi:ss'),' ')
,' ',decode(nvl((:report_in_days),' '),' ', to_date(to_char(sysdate
,'mm/dd/yyyy hh24:mi:ss'),'mm/dd/yyyy hh24:mi:ss'),(sysdate
- to_number(:report_in_days))),(:start_date))),'mm/dd/yyyy hh24:mi:ss')
,'mm/dd/yyyy hh24:mi:ss')
and to_date(to_char((decode(nvl(to_char(:end_date,'mm/dd/yyyy hh24:mi:ss'),' ')
,' ',decode(nvl((:report_in_days),' '),' ', to_date('12/31/4712 00:00:00'
,'mm/dd/yyyy hh24:mi:ss'),(sysdate)),(:end_date))),'mm/dd/yyyy hh24:mi:ss')
,'mm/dd/yyyy hh24:mi:ss')
