select transaction_id
,console_transaction_id
,status_category
,created_by
,creation_date
,object_name
,process_category
,change_effective_date
,CONSOLE_ENTRY_ID
,CONSOLE_TXN_STATUS
,status
,object
,issue_id
,issue_type_code
,ISSUE_MODULE_COMP_INSTANCE_ID
,ISSUE_MODULE_COMPONENT_NAME
,FAULT_ID
,FAULT_CODE
,ERROR_CODE
,subject_id
,FULL_NAME
,ass_attribute1
,assignment_number
,EXTRACTVALUE(Value,'/TRANSACTION/COMPAREDATA/CompareVO/AssignmentTerminationWhenAn
dWhyVORow/Compare[AttributeName = "ActionCode"]/NewValue') Action_Code
,EXTRACTVALUE(Value,'/TRANSACTION/COMPAREDATA/CompareVO/AssignmentTerminationWhenAn
dWhyVORow/Compare[AttributeName = "ActionReasonCode"]/NewValue') Action_Reason_Code
,EXTRACTVALUE(Value,'/TRANSACTION/COMPAREDATA/CompareVO/WorkRelationshipMVORow/
Compare[Label = "Termination Date"]/NewValue') Term_date
,EXTRACTVALUE(Value,'/TRANSACTION/COMPAREDATA/CompareVO/WorkRelationshipMVORow/
Compare[AttributeName = "RehireRecommendation"]/NewValue') Rehire_REcomend
,EXTRACTVALUE(Value,'/TRANSACTION/COMPAREDATA/CompareVO/WorkRelationshipDFFVORow/
Compare[AttributeName = "regrettable"]/NewValue') regrettable
,EXTRACTVALUE(Value,'/TRANSACTION/COMPAREDATA/CompareVO/WorkRelationshipMVORow/
Compare[AttributeName = "RevokeUserAccess"]/NewValue') RevokeUserAccess
from
(
SELECT
htce.transaction_id
,fcti.console_transaction_id
,htce.status_category
,htce.created_by
,to_char(htce.creation_date,'MM/DD/YYYY') creation_date
,htce.object_name
,htce.process_category
,to_char(htce.change_effective_date,'MM/DD/YYYY') change_effective_date
,htce.CONSOLE_ENTRY_ID
,htce.CONSOLE_TXN_STATUS
,htd.status
,hth.object
,fci.issue_id
,fci.issue_type_code
,fci.ISSUE_MODULE_COMP_INSTANCE_ID
,fci.ISSUE_MODULE_COMPONENT_NAME
,fci.FAULT_ID
,fci.FAULT_CODE
,fci.ERROR_CODE
-- ,fci.FAULT_PAYLOAD
,hth.subject_id
,ppnf.FULL_NAME
,paam.ass_attribute1
,paam.assignment_number
,hth.XML_DATA_CACHE value
FROM fusion.hrc_txn_console_entry htce
,fusion.hrc_txn_header hth
,fusion.hrc_txn_data htd
,FUSION.FND_CONSOLE_TRANSACTION_INFO fcti
,FUSION.FND_CONSOLE_ISSUE fci
,per_all_assignments_m paam
,PER_PERSON_NAMES_F ppnf
WHERE hth.transaction_id = htd.transaction_id
AND htd.transaction_id = htce.transaction_id
-- and htce.process_category = 'Terminate Employment'
-- and htce.console_txn_status = 'ORA_HRC_TXN_ERRORED'
and fcti.TRANSACTION_ID = htce.Transaction_Id
and ppnf.person_id = hth.subject_id
and ppnf.name_type = 'GLOBAL'
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and paam.person_id = ppnf.person_id
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and paam.Assignment_Status_Type='ACTIVE'
and paam.effective_latest_change = 'Y'
and paam.assignment_type = 'E'
and paam.period_of_service_id in (select max(pps.period_of_service_id) from
per_periods_of_service pps where pps.person_id = paam.person_id)
and fci.CONSOLE_TRANSACTION_ID = fcti.CONSOLE_TRANSACTION_ID
and fci.FAULT_PAYLOAD like '%<NUMBER>PAY-1636340%'
and hth.module_identifier = 'AssignmentTermination'
-- and fci.issue_id = 300008106194009
--AND htd.transaction_id = 300008140503891
)
