组织成本关联OU、法人实体

SELECT hou.name

,hou.short_code

,ccg.cost_group

,ccga.organization_id

FROM hr_operating_units hou

,hr.hr_all_organization_units hrou

,hr.hr_organization_information hoi

,apps.xle_entity_profiles xep

,gl_legal_entities_bsvs gleb

,cst_cost_groups ccg --成本组

,cst_cost_group_assignments ccga --组织成本组关联

WHERE 1 = 1

AND hou.organization_id = 81 -- fnd_profile.value('DEFAULT_ORG_ID')

AND hou.organization_id = hrou.organization_id

AND hrou.organization_id = hoi.organization_id

AND hoi.org_information_context = 'Operating Unit Information'

AND hoi.org_information2 = xep.legal_entity_id

AND xep.legal_entity_id = gleb.legal_entity_id

AND trunc(SYSDATE) BETWEEN nvl(gleb.start_date

,trunc(SYSDATE)) AND nvl(gleb.end_date

,trunc(SYSDATE))

AND xep.legal_entity_id = ccg.legal_entity

AND ccg.cost_group_id = ccga.cost_group_id