Query to find the responsibility for quering supplier sites of a perticular operating unit in R12

Finding  the operating unit of a Supplier:

--------------------------------------------------------------

SELECT HZP.PARTY_NAME "VENDOR NAME",hou.name                       operating_unit

, APS.SEGMENT1 "VENDOR NUMBER"

, ASS.VENDOR_SITE_CODE "SITE CODE"

FROM HZ_PARTIES HZP

, AP_SUPPLIERS APS

, HZ_PARTY_SITES SITE_SUPP

, AP_SUPPLIER_SITES_ALL ASS

--, IBY_EXTERNAL_PAYEES_ALL IEP

, hr_all_organization_units       hou

WHERE HZP.PARTY_ID = APS.PARTY_ID

AND HZP.PARTY_ID = SITE_SUPP.PARTY_ID

AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID

AND ASS.VENDOR_ID = APS.VENDOR_ID

and  ass.org_id    = hou.organization_id

AND ASS.VENDOR_SITE_CODE='xxxxx'

 Finding  the responsibility  of a Supplier:

SELECT distinct B.PROFILE_OPTION_VALUE,C.RESPONSIBILITY_NAME,d.SECURITY_PROFILE_NAME, d1.name ou_name

FROM FND_PROFILE_OPTIONS_VL A

, fnd_profile_option_values b

, fnd_responsibility_vl c

, per_security_profiles_v d

, per_organization_structures a1

, PER_ORG_STRUCTURE_VERSIONS b1

, per_org_structure_elements_v c1

, hr_operating_units d1

WHERE A.user_PROFILE_OPTION_NAME LIKE 'MO%Security%'

and d.ORGANIZATION_STRUCTURE_ID = a1.ORGANIZATION_STRUCTURE_ID

and a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID

and TO_NUMBER(b.LEVEL_VALUE)=c.RESPONSIBILITY_ID

and d.SECURITY_PROFILE_ID = b.PROFILE_OPTION_VALUE

--AND upper(c.RESPONSIBILITY_NAME) like 'PAYABLES MANAGER'

and a1.ORGANIZATION_STRUCTURE_ID = b1.ORGANIZATION_STRUCTURE_ID

and b1.ORG_STRUCTURE_VERSION_ID= c1.ORG_STRUCTURE_VERSION_ID

and (d1.organization_id = c1.ORGANIZATION_ID_CHILD

or d1.organization_id = c1.ORGANIZATION_ID_parent)

AND d1.name='14101 Travelscape, LLC'

UNION ALL

SELECT B.PROFILE_OPTION_VALUE,C.RESPONSIBILITY_NAME,d.SECURITY_PROFILE_NAME, d1.name ou_name

FROM FND_PROFILE_OPTIONS_VL A

, fnd_profile_option_values b

, fnd_responsibility_vl c

, per_security_profiles_v d

,PER_SECURITY_ORGANIZATIONS_V c1

, hr_operating_units d1

WHERE A.user_PROFILE_OPTION_NAME LIKE 'MO%Security%'

and a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID

and TO_NUMBER(b.LEVEL_VALUE)=c.RESPONSIBILITY_ID

and d.SECURITY_PROFILE_ID = b.PROFILE_OPTION_VALUE

--AND upper(c.RESPONSIBILITY_NAME) like 'PAYABLES MANAGER'

AND c1.SECURITY_PROFILE_ID = d.SECURITY_PROFILE_ID

and c1.ORGANIZATION_ID = d1.ORGANIZATION_ID

AND d1.name='xxxxx'

--