Oracle EBS中分类账和责任人实体 的涉及(有sql语句实例)
2012-12-06 16:05 2822人阅读 评论(0) 收藏 举报
分类:
Oracle
EBS(12) Oracle数据库技术(6)
版权声明:本文为博主原创著作,未经博主允许不得转载。
第一,对于EBS中的法人实体和分类账以及OU之间的一个层次关系如下图:
其中,对于分类账和责任人实体,并不简单是一对多的关联,按照理论上来讲:由于分类账存在救助分类账,所以一个法人实体除了对应一个主分类账(Primary
Ledger)外,还可能存在救助分类账,可是一个总负责人实体肯定只对应一个唯一的主分类账,而对于分类账之间是否存在有“主从关系”还不太精通,有待进一步考证。
而在R12中,要找出她们之间的关联就需要通过瞬间sql来看了:
[c-sharp] view
plaincopy
- 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’);
[c-sharp] view
plain copy
- 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’);
从数额结果中得以看到,系统中有7个分类账(LEDGER)和5个法人实体(LEGAL_ENTITY),对于TCL_YSP这些法人实体来说,拥有五个分类账,其LEDGER_CATEGORY_CODE分别为PRIMARY和SECONDARY,表明了一个责任人实体有一个主分类账,并且能够有协理分类账,而2041这个分类账,则尚未对号入座的总负责人实体,然而其LEDGER_CATEGORY_CODE还是为PRIMARY,这表达一个分类账的category_code有可能是事先概念好的,而不是在与法人实体关联的时候才决定的,所以无法确定分类账之间到底有层次关系……
对上述的sql举办简要,也得以得出相应的关系来:
[c-sharp] view
plaincopy
- 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’;
[c-sharp] view
plain copy
- 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’;