1. Tables and Views for SCM
  2. MSC_AP_SALES_ORG_V

MSC_AP_SALES_ORG_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

ORGANIZATION_ID

ORGANIZATION_NAME

PARENT_ORGANIZATION_ID

PARENT_ORG_NAME

HIERARCHY_NAME

Query

SQL_Statement

SELECT distinct ttn.organization_id, ttn.organization_name, ttn.parent_pk1_value parent_organization_id, org.name parent_org_name, ttn.hierarchy_name

FROM

(SELECT tn.parent_pk1_value,

tn.pk1_start_value,

tn.depth,

h.name organization_name,

h.organization_id,

tv.TREE_VERSION_NAME hierarchy_name

FROM fnd_tree_node tn,

fnd_tree_version_vl tv,

HR_ALL_ORGANIZATION_UNITS_F_VL h

WHERE tn.tree_structure_code =tv.tree_structure_code

AND tn.tree_version_id = tv.tree_version_id

AND tn.tree_code = tv.tree_code

AND tn.tree_structure_code = 'RESOURCE_ORG_TREE_STRUCTURE'

AND TRUNC(sysdate) BETWEEN TRUNC(tv.effective_start_date)

AND TRUNC(tv.effective_end_date)

AND tv.status = 'ACTIVE'

AND tn.pk1_start_value = h.organization_id

) ttn , HR_ALL_ORGANIZATION_UNITS_F_VL org

where

ttn.parent_pk1_value = org.organization_id (+)

START WITH ttn.parent_pk1_value IS NULL

CONNECT BY PRIOR ttn.pk1_start_value=ttn.parent_pk1_value