PO Assign Approval Groups Query

select distinct hr.name "Operating Unit",
 --gs.CURRENCY_CODE,pap.EMPLOYEE_NUMBER,pap.full_name,
HR_GENERAL.DECODE_POSITION_LATEST_NAME(paa.POSITION_ID) Position,--pog.CONTROL_GROUP_NAME position,
hbg.BUSINESS_GROUP_NAME "Organization",
pj.name "Job",
pcf.CONTROL_FUNCTION_NAME "Document Type",
POG.CONTROL_GROUP_NAME "Approval Group" ,
ppc.START_DATE "Effective From",
ppc.END_DATE "Effective To"
--pcr.*
--pcr.RULE_TYPE_CODE,pcr.OBJECT_CODE,pcr.AMOUNT_LIMIT,
--segment1_high||segment2_high||segment3_high||segment4_high||segment5_high||segment6_high||segment7_high||segment8_high "High",
--segment1_low||segment2_low||segment3_low||segment4_low||segment5_low||segment6_low||segment7_low||segment8_low "low"
from po_control_groups_all pog,
po_control_functions pcf,
PO_POSITION_CONTROLS_all ppc,
PER_JOBS pj,
--PO_CONTROL_RULES pcr,
hr_operating_units hr,
PER_ALL_ASSIGNMENTS_F paa,
per_all_people_f pap,
hrfv_business_groups hbg
--gl_ledgers gs
where pog.CONTROL_GROUP_ID= ppc.CONTROL_GROUP_ID
and ppc.CONTROL_FUNCTION_ID=pcf.CONTROL_FUNCTION_ID
and pog.org_id=ppc.org_id
and pj.job_id=ppc.job_id
--and pcr.CONTROL_GROUP_ID=pog.CONTROL_GROUP_ID
--and nvl(pcr.INACTIVE_DATE,sysdate)>=sysdate
and hr.organization_id=pog.org_id
and paa.job_id=pj.job_id
and paa.person_id=pap.person_id
and  nvl(pap.EFFECTIVE_END_DATE,sysdate)>=sysdate
--and gs.ledger_id=hr.SET_OF_BOOKS_ID
and ppc.POSITION_ID=paa.position_id
and ppc.position_id is not null
and  nvl(paa.EFFECTIVE_END_DATE,sysdate)>=sysdate
and paa.ASSIGNMENT_STATUS_TYPE_ID=1
and hr.BUSINESS_GROUP_ID = hbg.BUSINESS_GROUP_ID
--and hr.name='OU_ENVS_BHA_US'
--and HR_GENERAL.DECODE_POSITION_LATEST_NAME(PAa.POSITION_ID) like 'SLA-MEM PERF  PL-OPR.%'
-- and rownum=1
order by hr.name--,pap.employee_number
,HR_GENERAL.DECODE_POSITION_LATEST_NAME(paa.POSITION_ID)
--,pcf.CONTROL_FUNCTION_NAME
,ppc.START_DATE

PO Approver Group Query

SELECT   HOU.NAME,
         PCGA.CONTROL_GROUP_NAME,
         PCGA.DESCRIPTION,
         PCR.OBJECT_CODE,
         PCR.RULE_TYPE_CODE,
         PCR.AMOUNT_LIMIT,
            PCR.SEGMENT1_LOW
         || '.'
         || PCR.SEGMENT2_LOW
         || '.'
         || PCR.SEGMENT3_LOW
         || '.'
         || PCR.SEGMENT4_LOW
         || '.'
         || PCR.SEGMENT5_LOW
         || '.'
         || PCR.SEGMENT6_LOW
         || '.'
         || PCR.SEGMENT7_LOW
         || '.'
         || PCR.SEGMENT8_LOW
            "LOW_VALUE",
            PCR.SEGMENT1_HIGH
         || '.'
         || PCR.SEGMENT2_HIGH
         || '.'
         || PCR.SEGMENT3_HIGH
         || '.'
         || PCR.SEGMENT4_HIGH
         || '.'
         || PCR.SEGMENT5_HIGH
         || '.'
         || PCR.SEGMENT6_HIGH
         || '.'
         || PCR.SEGMENT7_HIGH
         || '.'
         || PCR.SEGMENT8_HIGH
            "HIGH_VALUE"
  FROM   PO_CONTROL_GROUPS_ALL PCGA,
         PO_CONTROL_RULES PCR,
         HR_OPERATING_UNITS HOU
 WHERE       PCGA.CONTROL_GROUP_ID = PCR.CONTROL_GROUP_ID
             and PCGA.ORG_ID =HOU.ORGANIZATION_ID
         --AND PCGA.CONTROL_GROUP_NAME = 'ENVS Buyer AG'
         --AND PCGA.ORG_ID = 103
         order by HOU.NAME,
         PCGA.CONTROL_GROUP_NAME