帐套相关使用

.   客户化开发中的多帐套屏蔽

1)  得到当前OU的值。

使用：mo_global.get_current_org_id或者fnd_global.org_id

2)  根据组织id得到帐套id和公司名称。SQL语句为：

DECLARE  

   l_org_information3 VARCHAR2(150);--帐套id

        l_company_desc     VARCHAR2(150);--公司中文描述

  BEGIN

   SELECT o3.org_information3,o3.attribute3

     INTO l_org_information3，l_company_desc

     FROM hr_all_organization_units    o,

          hr_all_organization_units_tl otl,

          hr_organization_information  o2,

          hr_organization_information  o3

    WHERE o.organization_id   = o2.organization_id

      AND o.organization_id   = o3.organization_id

      AND o2.org_information_context || '' = 'CLASS'

      AND o3.org_information_context = 'Operating Unit Information'

      AND o2.org_information1 = 'OPERATING_UNIT'

      AND o2.org_information2 = 'Y'

      AND o.organization_id   = otl.organization_id

      AND otl.language        = USERENV('LANG')

      AND o.organization_id   = &p_org_id;--OU id

   END;

3)  得到本位币，SQL语句为：

DECLARE

l_local_currency_code VARCHAR2(15);--本位币

BEGIN

SELECT gsob.currency_code

    INTO l_local_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  = &p_org_id;--OU ID

END;  

4)  在程序中加上帐套和本位币的限制

2.   多帐套实现扩展

1)  得到帐套的SQL语句为：

SELECT * FROM gl_ledgers;

2)  得到法人的SQL语句为：

SELECT * FROM xle_entity_profiles

Oracle 会计科目结构决定了财务记帐的帐户，它采用了弹性

域的概念，是分段结构，最长可30个段，每段各取一值形成

一个组合，称为帐户。

优点：灵活、方便

各段含义

• 公司段：进行独立财务核算的单位。

• 部门段：进行费用核算的单位，也叫成本中心。

• 科目段：自然科目，分为资产、负债、费用、成本、
权益等类别。

• 备用段：根据需要启用，越多记帐越明细。

最多允许30个段。

会计科木存放在弹性域中

SELECT ffvc.flex_value, ffvc.description
  FROM fnd_flex_values_vl ffvc, fnd_flex_value_sets ffsc
 WHERE ffvc.flex_value_set_id = ffsc.flex_value_set_id
   AND ffsc.flex_value_set_name = 'HSST_COMPANY' --公司段
   AND ffvc.enabled_flag = 'Y'
   AND SYSDATE BETWEEN nvl(ffvc.start_date_active, SYSDATE) AND
       nvl(ffvc.end_date_active, SYSDATE + 1);

--成本中心
SELECT ffvd.flex_value, ffvd.description
  FROM fnd_flex_values_vl ffvd, fnd_flex_value_sets ffsd
 WHERE ffvd.flex_value_set_id = ffsd.flex_value_set_id
   AND ffsd.flex_value_set_name = 'HSST_DEPARTMENT' --部门段
   AND ffvd.enabled_flag = 'Y'
   AND SYSDATE BETWEEN nvl(ffvd.start_date_active, SYSDATE) AND
       nvl(ffvd.end_date_active, SYSDATE + 1);

--预估科目
SELECT ffv.flex_value, ffv.description, ffv.*
  FROM fnd_flex_values_vl ffv, fnd_flex_value_sets ffs
 WHERE ffv.flex_value_set_id = ffs.flex_value_set_id
   AND ffs.flex_value_set_name = 'HSST_ACCOUNT' --科目段
   AND ffv.enabled_flag = 'Y'
   AND SYSDATE BETWEEN nvl(ffv.start_date_active, SYSDATE) AND
       nvl(ffv.end_date_active, SYSDATE + 1);

获取账户的值和描述

--获取账户描述和值
DECLARE
  v_key_flex_code   := 'GL#';
  v_appl_short_name := 'SQLGL';
  v_application_id  := 101;

  v_ccid NUMBER := 3006; --查找的CCID
BEGIN
  v_sob_id := fnd_profile.value_wnps('GL_SET_OF_BKS_ID');
  SELECT chart_of_accounts_id
    INTO v_coa_id
    FROM gl_sets_of_books sob
   WHERE sob.set_of_books_id = v_sob_id;
END;

delim := fnd_flex_apis.get_segment_delimiter(x_application_id => v_application_id,
                                             x_id_flex_code   => v_key_flex_code,
                                             x_id_flex_num    => v_coa_id);

v_success := fnd_flex_keyval.validate_ccid(appl_short_name  => v_appl_short_name,
                                           key_flex_code    => v_key_flex_code,
                                           structure_number => v_coa_id,
                                           combination_id   => v_ccid);

IF v_success THEN
  --获取账户描述
  v_account_desc := fnd_flex_keyval.concatenated_descriptions;
  --获取账户值
  v_account_num := fnd_flex_keyval.concatenated_values;
END IF;

END;