组织架构查询脚本

select gl.ledger_id,
       gl.name ledger_name,
       substr(gl.name, 1, 4) com_num,
        xfiv.legal_entity_identifier legal_id,
        xfiv.name legal_name,
       hou.organization_id ou_id, --org_id
       hou.name  ou_name, --ou名称
       ood.organization_id  inv_id, --库存组织 id
       ood.organization_code  inv_code, --库存组织代码
       ood.ORGANIZATION_NAME inv_name
  from hr_organization_information  hoi, --组织分类表
       hr_operating_units           hou, --ou视图
       org_organization_definitions ood, --库存组织定义视图
       xle_firstparty_information_v xfiv, --法人实体视图
       gl_ledgers gl --账簿
where hoi.org_information1 = 'OPERATING_UNIT'
   and hoi.organization_id = hou.organization_id
   and ood.operating_unit= hoi.organization_id
   and ood.LEGAL_ENTITY=xfiv.legal_entity_id
   and hou.set_of_books_id=gl.ledger_id
order by gl.name