分类目录归档:GL模块

Oracle ebs控制科目输入(ADI+人工)方案

背景:
客户需要限制某些科目只能从接口进入系统,不能手工及类手工方式录入。

思路:

寻求标准方案;客户化ADI。

标准方案:

先查看科目值集处对科目属性的控制,其中有一个控制项是“第三方控制账户”,控制账户内可选择“限制人工日记帐”(此时想看能否可以选择来源进行控制,于是通过sql方式【select * from fnd_lookups t where t.meaning like ‘%限制人工日记%’】找到控制账户的lookup_type”GL_CONTROL_ACCOUNT_SOURCES”,发现不能新增来源),于是从字面理解是否只会对日记账来源为“人工”的进行控制。

测试结果:前台界面确实控制住不能录入,并弹窗提示“您不能使用控制账户。请选择其他账户”,满足需求。

然后通过ADI导入,无论ADI的来源选择何种,均会提示“您不能使用控制账户。请选择其他账户”,满足需求。

最后通过接口表的方式,通过设置过“限制人工日记账”的科目导入生成账务,可以正常生成日记账,满足需求。

完美解决问题,不再讨论客户化的方式。

Oracle EBS汇总模板建立问题处理

当建立总账汇总模板时,报错:

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

GLSTPM module: 增加/删除汇总账户
+---------------------------------------------------------------------------+

当前的系统时间为 24-08-2020 09:41:45

+---------------------------------------------------------------------------+


>> main() 24-08-2020 09:41:45

>> glsini() 24-08-2020 09:41:45
STPM0021: 默认优化系数为 0。

<< glsini() 24-08-2020 09:41:45

>> glstexs() 24-08-2020 09:41:45

<< glstexs() 24-08-2020 09:41:45

>> glsfst() 24-08-2020 09:41:45

>> open_cursor() 24-08-2020 09:41:45

<< open_cursor() 24-08-2020 09:41:45

>> load_segnum_vsid() 24-08-2020 09:41:45

<< load_segnum_vsid() 24-08-2020 09:41:45

>> do_fetch_templates() 24-08-2020 09:41:45
SHRD0108: 从 GL_SUMMARY_TEMPLATES 中取回 1 记录

<< do_fetch_templates() 24-08-2020 09:41:45

>> close_cursor() 24-08-2020 09:41:45

<< close_cursor() 24-08-2020 09:41:45

<< glsfst() 24-08-2020 09:41:45

>> glsltmp() 24-08-2020 09:41:45

>> gludlk() 24-08-2020 09:41:45

<< gludlk() 24-08-2020 09:41:45

<< glsltmp() 24-08-2020 09:41:45

>> glstexs() 24-08-2020 09:41:45

<< glstexs() 24-08-2020 09:41:45

>> glsuab() 24-08-2020 09:41:45

<< glsuab() 24-08-2020 09:41:45

>> glstadd() 24-08-2020 09:41:45

>> glucmt() 24-08-2020 09:41:45

<< glucmt() 24-08-2020 09:41:45
STPM0003: 已验证模板 "D-D-LEV1-D-D-D-D-D-D-D-D",并已定义全部累计组。

>> glsdsh() 24-08-2020 09:41:45
SHRD0119: 从 GL_SUMMARY_HIERARCHIES 中删除 0 记录。
SHRD0119: 从 GL_SUMMARY_HIERARCHY_INT 中删除 0 记录。

>> glucmt() 24-08-2020 09:41:45

<< glucmt() 24-08-2020 09:41:45

<< glsdsh() 24-08-2020 09:41:45

>> glsdah() 24-08-2020 09:41:45
SHRD0119: 从 GL_CODE_COMBINATIONS 中删除 0 记录。

>> glucmt() 24-08-2020 09:41:45

<< glucmt() 24-08-2020 09:41:45

<< glsdah() 24-08-2020 09:41:45

>> glsash() 24-08-2020 09:41:45
SHRD0117: 把 761 记录插入 GL_SUMMARY_HIERARCHIES

>> glucmt() 24-08-2020 09:41:45

<< glucmt() 24-08-2020 09:41:45

<< glsash() 24-08-2020 09:41:45

>> glsmah() 24-08-2020 09:41:45

>> gls_fill_seg_info() 24-08-2020 09:41:45

<< gls_fill_seg_info() 24-08-2020 09:41:45

>> gls_get_max_ccid() 24-08-2020 09:41:45

>> glucmt() 24-08-2020 09:41:45

<< glucmt() 24-08-2020 09:41:45

<< gls_get_max_ccid() 24-08-2020 09:41:45

>> glstmt() 24-08-2020 09:41:45

<< glstmt() 24-08-2020 09:41:45

>> gls_csa_rollup() 24-08-2020 09:41:45

>> glsgsmi() 24-08-2020 09:41:45
SHRD0114: 正在执行 Pro*C 语句 Insert into gl_sum_int ...

SHRD0043: ERROR: ORA-00001: 违反唯一约束条件 (GL.GL_SUMMARY_INT_2_U1)
         

<x glsgsmi() 24-08-2020 09:41:45

<x gls_csa_rollup() 24-08-2020 09:41:45
SHRD0075: 在 gls_csa_rollup() 的错误.

<x glsmah() 24-08-2020 09:41:45
SHRD0075: 在 glsmah() 的错误.

<x glstadd() 24-08-2020 09:41:45
SHRD0075: 在 glstadd_addtmp() 的错误.

问题处理:

通过脚本检查总账科目设置问题:

applprod:

@/u01/PROD/app/fs1/EBSapps/appl/gl/12.0.0/sql/glxacovl.sql

 

处理完直接的子父级后:

重新删除之前错误的模板,运行:
增加/删除汇总账户

运行完成后,重新新增模板即可。

具体sql如下:
SELECT vs.flex_value_set_name value_set_name,
fvh1.parent_flex_value ancestor,
gl_flexfields_pkg.get_parent_from_children(val.flex_value_set_id,
fvh1.parent_flex_value,
fvh1.child_flex_value_low,
fvh1.child_flex_value_high,
1) parent_flex_value,
fvh1.child_flex_value_low child_flex_value_low,
fvh1.child_flex_value_high child_flex_value_high,
gl_flexfields_pkg.get_parent_from_children(val.flex_value_set_id,
fvh1.parent_flex_value,
fvh2.child_flex_value_low,
fvh2.child_flex_value_high,
2) parent_flex_value2,
fvh2.child_flex_value_low child_flex_value_low2,
fvh2.child_flex_value_high child_flex_value_high2
FROM fnd_flex_values val,
fnd_flex_value_sets vs,
fnd_flex_value_hierarchies fvh1,
fnd_flex_value_hierarchies fvh2
WHERE val.structured_hierarchy_level IS NOT NULL
AND vs.flex_value_set_id = val.flex_value_set_id
AND fvh1.flex_value_set_id = val.flex_value_set_id
AND fvh1.parent_flex_value = val.flex_value
AND fvh2.flex_value_set_id = val.flex_value_set_id
AND fvh2.parent_flex_value = val.flex_value
AND fvh2.child_flex_value_low BETWEEN fvh1.child_flex_value_low AND fvh1.child_flex_value_high
AND (fvh2.child_flex_value_low <> fvh1.child_flex_value_low OR
fvh2.rowid < fvh1.rowid)
AND fvh2.rowid <> fvh1.rowid
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8

Oracle ebs冲销凭证报错APP-SQLGL-08087

错误如图:

原因:

此种现象是因为之前对凭证做过冲销,但是在未过账的时候,将生成的冲销凭证删除,但是冲销的记录在凭证头表字段accrual_rev_period_name留下记录,记录的内容是报错界面默认的冲销期间(2019-10)。

解决方案:

直接更新gl_je_headers表中字段accrual_rev_period_name为空即可正常冲销动作(更新前一定要备份,更新后重新期间可选)。

Oracle ebs 交叉验证规则导出

–已测试(两段均可),请变更账套名
select x.flex_validation_rule_name,
       x.description,
       h.include_exclude_indicator,
       h.concatenated_segments_low,
       h.concatenated_segments_high
  from apps.fnd_flex_vdation_rules_vl      x,
       apps.fnd_flex_validation_rule_lines h,
       gl_ledgers                          g
 where 1 = 1
   and x.id_flex_num = g.chart_of_accounts_id
   and x.flex_validation_rule_name = h.flex_validation_rule_name
   and (h.id_flex_code = 'GL#')
   and (h.application_id = 101)
   and g.name = 'FIN_Ledger'
 order by x.flex_validation_rule_name;

SELECT FFCV.APPLICATION_NAME,
       FFCV.FLEX_STRUCTURE_NAME,
       FFVV.FLEX_VALIDATION_RULE_NAME,
       FFVV.DESCRIPTION,
       FFVV.ERROR_MESSAGE_TEXT 错误信息,
       DECODE(FFVL.INCLUDE_EXCLUDE_INDICATOR, 'I', '包括', '排除') INCLUDE_EXCLUDE_INDICATOR,
       FFVL.CONCATENATED_SEGMENTS_LOW,
       FFVL.CONCATENATED_SEGMENTS_HIGH
  FROM FND_FLEX_CROSS_VALIDATION_V    FFCV,
       FND_FLEX_VDATION_RULES_VL      FFVV,
       FND_FLEX_VALIDATION_RULE_LINES FFVL
 WHERE 1 = 1
   AND FFCV.APPLICATION_ID = FFVV.APPLICATION_ID
   AND FFCV.FLEX_CODE = FFVV.ID_FLEX_CODE
   AND FFCV.FLEX_STRUCTURE_NUM = FFVV.ID_FLEX_NUM
   AND FFVV.APPLICATION_ID = FFVL.APPLICATION_ID
   AND FFVV.ID_FLEX_CODE = FFVL.ID_FLEX_CODE
   AND FFVV.ID_FLEX_NUM = FFVL.ID_FLEX_NUM
   AND FFVV.FLEX_VALIDATION_RULE_NAME = FFVL.FLEX_VALIDATION_RULE_NAME
   AND FFCV.APPLICATION_ID = 101
   AND FFVV.ENABLED_FLAG = 'Y'
   AND FFVL.ENABLED_FLAG = 'Y';

Oracle EBS查找帐套本位币及币种间汇率转换的方法

(1)获取帐套本位币

--取得当前帐套本位币
SELECT gsob.currency_code
  FROM gl_sets_of_books   gsob,
       hr_operating_units hou
 WHERE gsob.set_of_books_id = hou.set_of_books_id
   AND hou.organization_id = fnd_profile.value('ORG_ID');

(2)币种间转换

--取得指定币种(例如:HKD)当天转向当前帐套本位币(例如:CNY)的汇率
SELECT gdr.conversion_rate
  FROM gl_daily_rates_v gdr
 WHERE gdr.from_currency = 'HKD'
   AND gdr.to_currency IN (SELECT gsob.currency_code
                             FROM gl_sets_of_books   gsob,
                                  hr_operating_units hou
                            WHERE gsob.set_of_books_id = hou.set_of_books_id
                              AND hou.organization_id = fnd_profile.value('ORG_ID'))
   AND gdr.conversion_type = 'Corporate'
   AND gdr.conversion_date = trunc(SYSDATE);

(3)调用EBS的API来转换

SELECT gl_currency_api.get_rate(x_from_currency   => 'HKD',
                                x_to_currency     => 'CNY',
                                x_conversion_date => trunc(SYSDATE),
                                x_conversion_type => 'Corporate') from dual;