1. Tables and Views for Financials
  2. XLE_ASSOC_BUSINESS_ENTITIES_V

XLE_ASSOC_BUSINESS_ENTITIES_V

Details

  • Schema: FUSION

  • Object owner: XLE

  • Object type: VIEW

Columns

Name

ENTITY_TYPE

ENTITY_ID

ASSOCIATION_TYPE_ID

ORGANIZATION_NAME

DESCRIPTION

LOCATION

INVENTORY_ORGANIZATION

COUNTRY

ADDRESS

ORGANIZATION_ID

Query

SQL_Statement

SELECT entity_type,

entity_id,

assoc_types.association_type_id,

organization_name,

description,

location,

inventory_organization,

country,

address,

organization_id

FROM

(SELECT 'OPERATING_UNIT' entity_type ,

bu.BU_ID entity_id ,

bu.BU_NAME organization_name ,

loc.description description ,

loc.location_code location ,

'' inventory_organization,

(SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code = loc.country) country,

loc.address_line_1 || ' ' || loc.town_or_city || ' ' || loc.region_1 || ' ' || loc.postal_code address,

bu.BU_ID organization_id

from fun_all_business_units_v bu, hr_locations loc

where bu.location_id = loc.location_id

UNION

SELECT 'SHIP_TO_LOCATION' entity_type,

loc.location_id entity_id,

loc.location_code organization_name,

loc.description description,

loc.location_code location,

'' inventory_organization,

f.territory_short_name country,

loc.address_line_1 || ' ' || loc.town_or_city || ' ' || loc.region_1 || ' ' || loc.postal_code address,

location_id organization_id

FROM hr_locations loc,

fnd_territories_vl f

WHERE loc.ship_to_site_flag = 'Y'

AND f.territory_code = loc.country

AND sysdate < nvl(effective_end_date, sysdate + 1)

UNION

SELECT 'BILL_TO_LOCATION' entity_type,

loc.location_id entity_id ,

loc.location_code organization_name,

loc.description description ,

loc.location_code location ,

'' inventory_organization,

f.territory_short_name country,

loc.address_line_1 || ' ' || loc.town_or_city || ' ' || loc.region_1 || ' ' || loc.postal_code address,

location_id organization_id

FROM hr_locations loc, fnd_territories_vl f

WHERE loc.bill_to_site_flag = 'Y'

AND f.territory_code = loc.country

AND sysdate < nvl(effective_end_date, sysdate + 1)

UNION

SELECT 'INVENTORY_ORGANIZATION' entity_type ,

invo.organization_id entity_id ,

invo.organization_name organization_name ,

l.description description ,

l.location_code location ,

'' inventory_organization ,

(SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code = l.country ) country,

l.address_line_1 || ' ' || l.town_or_city || ' ' || l.region_1 || ' ' || l.postal_code "ADDRESS",

invo.organization_id organization_id

FROM inv_organization_definitions_v invo, hr_locations l

WHERE invo.location_id = l.location_id

UNION

SELECT 'INVENTORY_LOCATION' entity_type,

l.location_id entity_id,

l.location_code organization_name,

l.description description,

l.location_code location,

ood.organization_name inventory_organization,

(SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code = l.country) country,

l.address_line_1 || ' ' || l.town_or_city || ' ' || l.region_1 || ' ' || l.postal_code "ADDRESS",

l.location_id organization_id

FROM hr_locations l,INV_ORGANIZATION_DEFINITIONS_V ood

WHERE l.inventory_organization_id = ood.organization_id

AND sysdate < nvl(effective_end_date, sysdate + 1))

xle_assoc_bus_entities,

xle_assoc_object_types assoc_subj,

xle_association_types assoc_types

WHERE entity_type = assoc_subj.name

AND assoc_subj.object_type_id = assoc_types.object_type_id