HR相关表

表说明

org_organization_definitions

说明库存组织表是个视图

主键organization_id

关联关系

主要字段

列明	说明
organization_id	组织id
business_group_id
user_definition_enable_date
disable_date	失效时间
organization_code	组织代码
organization_name	组织名称
set_of_books_id	账簿idgl_sets_of_books.set_of_books_id
chart_of_accounts_id	账号id一般查询ccid使用。gl_sets_of_books.set_of_books_id
inventory_enabled_flag
operating_unit	对应的ou的idHr_Operating_Units.organization_id
legal_entity	法人实体ID,xle_entity_profiles.LEGAL_ENDTITY_ID对应le.NAME

Hr_Operating_Units

说明ou主要信息

主键organization_id

关联关系

主要字段

列明	说明
business_group_id
organization_id	ou id,ood.operating_unit
name	名称
date_from
date_to
short_code	简称
set_of_books_id	账簿idgl_sets_of_books.set_of_books_id
default_legal_context_id	默认法人实体,法人实体
usable_flag

xle_entity_profiles 

说明法人实体表

主键organization_id

关联关系

主要字段

列明	说明
LEGAL_ENTITY_ID	法人实体主键
name	法人实体名称

其他起效失效时间就不列出来了太特么麻烦了手残

Gl_Ledger_Config_Details

说明法人实体配置明细与其他相关信息关联用表其实直接用这个表也基本能找到法人实体信息

比起Xle_Entity_ProfilesGl_Ledger_Config_Details反而更重要的样子

主键object_id

关联关系

主要字段

列明	说明
Object_Id	对应Xle_Entity_Profiles.Legal_Entity_Id,也就是法人实体id 也关联Xle_Registrations.Source_Id,Reg.Source_Table(+) = 'XLE_ENTITY_PROFILES'  AND Reg.Identifying_Flag(+) = 'Y' 注意Xle_Registrations外连接

Gl_Ledgers

说明分类账主表一般分类账、财务等信息跟这里都有关联

主键ledger_id

关联关系

主要字段

列明	说明
ledger_id	主键
name	名称
short_name	简称
description	描述
chart_of_accounts_id	账号id查询ccid比较常用
period_set_name	设置期间名

        还有很多字段没一一加上懒

表关系

法人实体、分类账关系

SELECT Lg.Ledger_Id
      ,Lg.Name                       Ledger_Name
      ,Lg.Short_Name                 Ledger_Short_Name
      ,Cfgdet.Object_Id              Legal_Entity_Id
      ,Le.Name                       Legal_Entity_Name
      ,Reg.Location_Id               Location_Id
      ,Hrloctl.Location_Code         Location_Code
      ,Hrloctl.Description           Location_Description
      ,Lg.Ledger_Category_Code
      ,Lg.Currency_Code
      ,Lg.Chart_Of_Accounts_Id
      ,Lg.Period_Set_Name
      ,Lg.Accounted_Period_Type
      ,Lg.Sla_Accounting_Method_Code
      ,Lg.Sla_Accounting_Method_Type
      ,Lg.Bal_Seg_Value_Option_Code
      ,Lg.Bal_Seg_Column_Name
      ,Lg.Bal_Seg_Value_Set_Id
      ,Cfg.Acctg_Environment_Code
      ,Cfg.Configuration_Id
      ,Rs.Primary_Ledger_Id
      ,Rs.Relationship_Enabled_Flag
  FROM Gl_Ledger_Config_Details Primdet
      ,Gl_Ledgers               Lg
      ,Gl_Ledger_Relationships  Rs
      ,Gl_Ledger_Configurations Cfg
      ,Gl_Ledger_Config_Details Cfgdet
      ,Xle_Entity_Profiles      Le
      ,Xle_Registrations        Reg
      ,Hr_Locations_All_Tl      Hrloctl
 WHERE Rs.Application_Id = 101
   AND ((Rs.Target_Ledger_Category_Code = 'SECONDARY' AND Rs.Relationship_Type_Code <> 'NONE') OR
       (Rs.Target_Ledger_Category_Code = 'PRIMARY' AND Rs.Relationship_Type_Code = 'NONE') OR
       (Rs.Target_Ledger_Category_Code = 'ALC' AND
       Rs.Relationship_Type_Code IN ('JOURNAL', 'SUBLEDGER')))
   AND Lg.Ledger_Id = Rs.Target_Ledger_Id
   AND Lg.Ledger_Category_Code = Rs.Target_Ledger_Category_Code
   AND Nvl(Lg.Complete_Flag, 'Y') = 'Y'
   AND Primdet.Object_Id = Rs.Primary_Ledger_Id
   AND Primdet.Object_Type_Code = 'PRIMARY'
   AND Primdet.Setup_Step_Code = 'NONE'
   AND Cfg.Configuration_Id = Primdet.Configuration_Id
   AND Cfgdet.Configuration_Id(+) = Cfg.Configuration_Id
   AND Cfgdet.Object_Type_Code(+) = 'LEGAL_ENTITY'
   AND Le.Legal_Entity_Id(+) = Cfgdet.Object_Id
   AND Reg.Source_Id(+) = Cfgdet.Object_Id
   AND Reg.Source_Table(+) = 'XLE_ENTITY_PROFILES'
   AND Reg.Identifying_Flag(+) = 'Y'
   AND Hrloctl.Location_Id(+) = Reg.Location_Id
   AND Hrloctl.Language(+) = Userenv('LANG');

法人实体、分类账关系简化版没有描述

SELECT Lg.Ledger_Id --分类帐      
      ,Cfgdet.Object_Id Legal_Entity_Id --法人实体
      ,Lg.Currency_Code
      ,Lg.Chart_Of_Accounts_Id
      ,Rs.Primary_Ledger_Id
  FROM Gl_Ledger_Config_Details Primdet
      ,Gl_Ledgers               Lg
      ,Gl_Ledger_Relationships  Rs
      ,Gl_Ledger_Configurations Cfg
      ,Gl_Ledger_Config_Details Cfgdet
 WHERE Rs.Application_Id = 101 --101为总账GL应用      
   AND ((Rs.Target_Ledger_Category_Code = 'SECONDARY' AND Rs.Relationship_Type_Code <> 'NONE') OR
       (Rs.Target_Ledger_Category_Code = 'PRIMARY' AND Rs.Relationship_Type_Code = 'NONE') OR
       (Rs.Target_Ledger_Category_Code = 'ALC' AND
       Rs.Relationship_Type_Code IN ('JOURNAL', 'SUBLEDGER')))
   AND Lg.Ledger_Id = Rs.Target_Ledger_Id
   AND Lg.Ledger_Category_Code = Rs.Target_Ledger_Category_Code
   AND Nvl(Lg.Complete_Flag, 'Y') = 'Y'
   AND Primdet.Object_Id = Rs.Primary_Ledger_Id
   AND Primdet.Object_Type_Code = 'PRIMARY'
   AND Primdet.Setup_Step_Code = 'NONE'
   AND Cfg.Configuration_Id = Primdet.Configuration_Id
   AND Cfgdet.Configuration_Id(+) = Cfg.Configuration_Id
   AND Cfgdet.Object_Type_Code(+) = 'LEGAL_ENTITY';