Script for Responsibility - ORG Relationship

-- Query OU given the inv_org name
SELECT HOU.NAME OPERATING_UNIT_NAME,                      
--hou.short_code,
         HOU.ORGANIZATION_ID OPERATING_UNIT_ID,
         HOU.SET_OF_BOOKS_ID,
         HOU.BUSINESS_GROUP_ID,
         OOD.ORGANIZATION_NAME INVENTORY_ORGANIZATION_NAME,
         OOD.ORGANIZATION_CODE INV_ORGANIZATION_CODE,
         OOD.ORGANIZATION_ID INV_ORGANIZATION_ID,
         OOD.CHART_OF_ACCOUNTS_ID
    FROM APPS.HR_OPERATING_UNITS HOU, APPS.ORG_ORGANIZATION_DEFINITIONS OOD
   WHERE     1 = 1
         AND HOU.ORGANIZATION_ID = OOD.OPERATING_UNIT
         AND OOD.ORGANIZATION_CODE IN ('XXX')
ORDER BY HOU.ORGANIZATION_ID ASC

-- Query OU given the responsibility name
SELECT frv.responsibility_name, fpov.profile_option_value org_id, hou.NAME
FROM apps.hr_organization_units hou,
apps.fnd_profile_options_vl fpo,
apps.fnd_profile_option_values fpov,
apps.fnd_responsibility_vl frv
WHERE frv.responsibility_name = :p_resp_name
AND fpov.level_value = frv.responsibility_id
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = 'MO: Operating Unit'
AND fpov.profile_option_id = fpo.profile_option_id
AND hou.organization_id = TO_NUMBER (fpov.profile_option_value)
ORDER BY frv.responsibility_name

-- Query responsibilities given the OU name
SELECT frv.responsibility_name, fpov.profile_option_value org_id, hou.NAME
FROM apps.fnd_profile_options_vl fpo,
apps.fnd_responsibility_vl frv,
apps.fnd_profile_option_values fpov,
apps.hr_organization_units hou
WHERE hou.NAME = :p_ou_name
AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = 'MO: Operating Unit'
AND frv.responsibility_id = fpov.level_value
ORDER BY frv.responsibility_name

--Query inventory Orgs from OU name
SELECT hou.NAME operating_unit_name, --hou.short_code,
hou.organization_id operating_unit_id, hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code, ood.organization_id Inv_organization_id, ood.chart_of_accounts_id
FROM hr_operating_units hou, org_organization_definitions ood
WHERE 1 = 1 AND hou.organization_id = ood.operating_unit
--and ood.organization_code in ('XXX')
and hou.NAME like '%XXXX%'
ORDER BY hou.organization_id ASC