Fetch Goals approval status from fusion transaction tables
Content
We are trying to fetch the goal and goal plan details which is submitted for approval (Pending approval, Draft) from transaction tables.
We are able to fetch only one goal and plan details from below sample query
SELECT
,XMLTYPE(txndata.data_cache).EXTRACT('TRANSACTION[1]/BUSINESSDATA[1]/AM[1]/TXN[1]/EO[1]/GoalEORow[1]/PersonId[1]/DATA/text()').getStringVal() AS person_id
,XMLTYPE(txndata.data_cache).EXTRACT('TRANSACTION[1]/BUSINESSDATA[1]/AM[1]/TXN[1]/EO[1]/GoalEORow[1]/GoalName[1]/DATA/text()').getStringVal() AS goal_name
,nvl(EXTRACTVALUE(xmltype('' || txndata.data_cache || ''), 'TRANSACTION/BUSINESSDATA/AM/TXN/EO/GoalPlanGoalEORow/GoalPlanId/DATA'),'') AS goal_plan_id
,XMLTYPE(txndata.data_cache).EXTRACT('TRANSACTION[1]/BUSINESSDATA[1]/AM[1]/TXN[1]/EO[1]/GoalPlanGoalEORow[1]/Weighting[1]/DATA/text()').getStringVal() AS weighting
Tagged:
1