SELECT hou.NAME operating_unit, pj.NAME job, pcf.control_function_name,
pcga.control_group_name,
TO_CHAR (ppca.start_date, 'DD-MON-YY') start_date,
TO_CHAR (ppca.end_date, 'DD-MON-YY') end_date
FROM apps.po_position_controls_all ppca,
apps.po_control_functions pcf,
apps.hr_operating_units hou,
apps.per_jobs_tl pj,
apps.po_control_groups_all pcga
WHERE ppca.control_function_id = pcf.control_function_id
AND ppca.org_id = hou.organization_id
AND ppca.job_id = pj.job_id
AND pj.LANGUAGE = 'US'
AND pj.source_lang = 'US'
AND pcga.control_group_id = ppca.control_group_id
AND ppca.org_id = :ou_name
pcga.control_group_name,
TO_CHAR (ppca.start_date, 'DD-MON-YY') start_date,
TO_CHAR (ppca.end_date, 'DD-MON-YY') end_date
FROM apps.po_position_controls_all ppca,
apps.po_control_functions pcf,
apps.hr_operating_units hou,
apps.per_jobs_tl pj,
apps.po_control_groups_all pcga
WHERE ppca.control_function_id = pcf.control_function_id
AND ppca.org_id = hou.organization_id
AND ppca.job_id = pj.job_id
AND pj.LANGUAGE = 'US'
AND pj.source_lang = 'US'
AND pcga.control_group_id = ppca.control_group_id
AND ppca.org_id = :ou_name
This comment has been removed by the author.
ReplyDeleteThis is a very useful query for extracting approval assignment details in Oracle EBS, especially with the combination of tables like po_position_controls_all, po_control_functions, and HR job data. It’s helpful to see how operating unit, job, control function, and control group are linked together in a single query.
ReplyDeleteSuch queries are really valuable for audit, troubleshooting approval hierarchies, and understanding how position-based approvals are configured. It would be great if you could also share a version that includes employee or approver names for more complete visibility.
Thanks for sharing this practical SQL!
https://atechreview.com/