验证账户组合是否有效
（科目开始结束时间、科目是否启用、账户组合开始结束时间、账户组合是否有效）
image.png
SELECT gcc.code_combination_id --账户组合ID
,gcc.last_update_date --更新时间
,gcc.last_updated_by --更新
,gcc.start_date_active --账户组合开始时间
,gcc.end_date_active --账户组合结束时间
,gcc.chart_of_accounts_id --科目COA
,gcc.detail_posting_allowed_flag
,gcc.detail_budgeting_allowed_flag
,gcc.account_type --科目类型
,gcc.enabled_flag --账户是否启用（Y/N）
,gcc.summary_flag --是否汇总科目（Y/N）
,ffv.enabled_flag km_enabled_flag --科目是否启用（Y/N）
,ffv.start_date_active km_start_date --科目开始时间
,ffv.end_date_active km_end_date --科目结束时间
,gcc.segment1
,gcc.segment2
,gcc.segment3
,gcc.segment4
,gcc.segment5
,gcc.segment6
,gcc.segment7
,gcc.segment8
,gcc.segment9
,cux_common_utl.get_independent_value_set_desc('DTMY_COMPANY'
,NULL
,gcc.segment1
,NULL) seg1_name
,cux_common_utl.get_independent_value_set_desc('DTMY_DEPARTMENT'
,NULL
,gcc.segment2
,NULL) seg2_name
,cux_common_utl.get_independent_value_set_desc('DTMY_ACCOUNT'
,NULL
,gcc.segment3
,NULL) seg3_name
,cux_common_utl.get_independent_value_set_desc('DTMY_SUBACCOUNT'
,NULL
,gcc.segment4
,NULL) seg4_name
,cux_common_utl.get_independent_value_set_desc('DTMY_PROJECT'
,NULL
,gcc.segment5
,NULL) seg5_name
,cux_common_utl.get_independent_value_set_desc('DTMY_PRODUCT'
,NULL
,gcc.segment6
,NULL) seg6_name
,cux_common_utl.get_independent_value_set_desc('DTMY_INTERCOMPANY'
,NULL
,gcc.segment7
,NULL) seg7_name
,cux_common_utl.get_independent_value_set_desc('DTMY_SPACE1'
,NULL
,gcc.segment8
,NULL) seg8_name
,cux_common_utl.get_independent_value_set_desc('DTMY_SPACE2 '
,NULL
,gcc.segment9
,NULL) seg9_name
,gcck.concatenated_segments acc_full_code --账户代码组合
,cux_public_pkg.get_gl_ccid_desc(gcc.chart_of_accounts_id
,gcc.code_combination_id) ACC_FULL_NAME --账户代码组合说明
,gcc.description
FROM gl_code_combinations gcc
,fnd_flex_value_sets ffvs
,fnd_flex_values_vl ffv
,gl_code_combinations_kfv gcck
WHERE 1 = 1
AND gcc.code_combination_id = gcck.code_combination_id
AND ffvs.flex_value_set_name = 'DTMY_ACCOUNT'
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.flex_value = gcc.segment3;
上述使用到的function取账户组合和科目段描述的
/* =============================================
* FUNCTION
* NAME get_independent_value_set_desc
* DESCRIPTION:
* 值集是EBS开发人员常打交道的应用产品
* 本过程帮助开发人员方便的获取独立值集的值对应的描述
* Argument:
* p_value_set_name 值集名称
* p_value_set_id 值集名ID
* p_flex_value 值
* p_flex_value_id 值ID
* RETURN:
* 值描述
* HISTORY:
*
* =============================================*/
FUNCTION get_independent_value_set_desc(p_value_set_name IN VARCHAR2
,p_value_set_id IN NUMBER DEFAULT NULL
,p_flex_value IN VARCHAR2
,p_flex_value_id IN NUMBER DEFAULT NULL)
RETURN VARCHAR2 IS
l_description VARCHAR2(240);
BEGIN
SELECT ffv.description
INTO l_description
FROM fnd_flex_values_vl ffv
,fnd_flex_value_sets ffvs
WHERE 1 = 1
AND SYSDATE BETWEEN nvl(ffv.start_date_active
,SYSDATE - 1) AND
nvl(ffv.end_date_active
,SYSDATE + 1)
AND ffv.flex_value = nvl(p_flex_value
,ffv.flex_value)
AND ffv.flex_value_id = nvl(p_flex_value_id
,ffv.flex_value_id)
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.enabled_flag = 'Y'
AND ffvs.flex_value_set_name =
nvl(p_value_set_name
,ffvs.flex_value_set_name)
AND ffv.flex_value_set_id =
nvl(p_value_set_id
,ffv.flex_value_set_id);
RETURN l_description;
EXCEPTION
WHEN OTHERS THEN
RETURN l_description;
END get_independent_value_set_desc;
--取得科目组合全部段的描述值
FUNCTION get_gl_ccid_desc(p_coa_id NUMBER
,p_ccid NUMBER) RETURN VARCHAR2 IS
l_count NUMBER;
l_segment VARCHAR2(1000);
BEGIN
SELECT COUNT(*)
INTO l_count
FROM fnd_id_flex_segments fif
WHERE fif.id_flex_code = 'GL#'
AND fif.id_flex_num = p_coa_id
AND fif.enabled_flag = 'Y'
ORDER BY fif.segment_num;
FOR i IN 1 .. l_count LOOP
IF i = 1 THEN
l_segment := get_flex_seg_desc(p_appl_short_name => 'SQLGL'
,p_key_flex_code => 'GL#'
,p_structure_number => p_coa_id
,p_combination_id => p_ccid
,p_seg_number => i);
ELSE
l_segment := l_segment || '.' ||
get_flex_seg_desc(p_appl_short_name => 'SQLGL'
,p_key_flex_code => 'GL#'
,p_structure_number => p_coa_id
,p_combination_id => p_ccid
,p_seg_number => i);
END IF;
END LOOP;
RETURN(l_segment);
EXCEPTION
WHEN OTHERS THEN
RETURN '';
END;
--取键弹性域某一段的描述
FUNCTION get_flex_seg_desc(p_appl_short_name VARCHAR2
,p_key_flex_code VARCHAR2
,p_structure_number VARCHAR2
,p_combination_id NUMBER
,p_seg_number NUMBER) RETURN VARCHAR2 IS
v_segment_desc VARCHAR2(2000) := NULL;
v_find BOOLEAN;
BEGIN
---------效验CCID的有效性
v_find := fnd_flex_keyval.validate_ccid(appl_short_name => p_appl_short_name
, --'SQLGL',
key_flex_code => p_key_flex_code
, --'GL#',
structure_number => p_structure_number
, --:WORLD.CHART_OF_ACCOUNTS_ID,
combination_id => p_combination_id --:COST_MATOUT_LINES.TXN_DISP_ID
);
---------对于有效的CCID,返回其全部段值的描述
IF v_find THEN
v_segment_desc := fnd_flex_keyval.segment_description(segnum => p_seg_number);
END IF;
RETURN v_segment_desc;
EXCEPTION
WHEN OTHERS THEN
RETURN '';
END get_flex_seg_desc;
--取科目组合
FUNCTION get_gl_flex_code(p_chart_of_accounts_id NUMBER
,p_ccid NUMBER) RETURN VARCHAR2 IS
l_flex_code VARCHAR2(2000);
BEGIN
l_flex_code := fnd_flex_ext.get_segs(application_short_name => 'SQLGL'
,key_flex_code => 'GL#'
,structure_number => p_chart_of_accounts_id
,combination_id => p_ccid);
IF l_flex_code IS NOT NULL THEN
RETURN(l_flex_code);
ELSE
RETURN('');
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN '';
END;
网友评论