CREATE OR REPLACE FUNCTION MEW_GET_ACCOUNT_DESC_FNC(P_CHART_OF_ACCOUNTS_ID IN NUMBER --科目结构ID
,P_CODE_COMBINATION_ID IN VARCHAR2 --科目ID,P_SEGMENT IN VARCHAR2 --子值集段值
,P_SEGMENT_NUM IN NUMBER) --子段值序号
RETURN VARCHAR2 IS
V_DESC VARCHAR2(240);
V_VALUESET_ID NUMBER;
V_PARENT_VALUESET_ID NUMBER;
V_DEPEND_SEGMENT_NUMBER NUMBER;
V_PARENT_SEGMENT_VALUE VARCHAR2(30);
BEGIN
--获取当前段值对应的值集ID
SELECT FIV.FLEX_VALUE_SET_ID
INTO V_VALUESET_ID
FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
AND (ID_FLEX_CODE = 'GL#')
AND (APPLICATION_ID = 101)
AND FIV.SEGMENT_NUM = P_SEGMENT_NUM;
--判断当前值集是否存在依赖关系
BEGIN
SELECT FFVS.PARENT_FLEX_VALUE_SET_ID
INTO V_PARENT_VALUESET_ID
FROM FND_FLEX_VALUE_SETS FFVS
WHERE FFVS.FLEX_VALUE_SET_ID = V_VALUESET_ID
AND FFVS.PARENT_FLEX_VALUE_SET_ID IS NOT NULL;
--存在依赖关系 判断依赖哪一段的值集
SELECT FIV.SEGMENT_NUM
INTO V_DEPEND_SEGMENT_NUMBER
FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
AND (ID_FLEX_CODE = 'GL#')
AND (APPLICATION_ID = 101)
AND FIV.FLEX_VALUE_SET_ID = V_PARENT_VALUESET_ID;
--获取依赖值集段值
SELECT DECODE(V_DEPEND_SEGMENT_NUMBER
,1
,SEGMENT1
,2
,SEGMENT2
,3
,SEGMENT3
,4
,SEGMENT4
,5
,SEGMENT5
,6
,SEGMENT6)
INTO V_PARENT_SEGMENT_VALUE
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID = P_CODE_COMBINATION_ID;
--获取当前段值描述
SELECT DESCRIPTION
INTO V_DESC
FROM APPS.FND_FLEX_VALUES_VL FFV
WHERE FFV.FLEX_VALUE_SET_ID IN
(SELECT FIV.FLEX_VALUE_SET_ID
FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
AND (ID_FLEX_CODE = 'GL#')
AND (APPLICATION_ID = 101)
AND FIV.SEGMENT_NUM = P_SEGMENT_NUM) --子值集段序号
AND FFV.FLEX_VALUE = P_SEGMENT --子值集段值
AND FFV.PARENT_FLEX_VALUE_LOW = V_PARENT_SEGMENT_VALUE --值集依赖(父值集段值)
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--不存在依赖关系
BEGIN
SELECT DESCRIPTION
INTO V_DESC
FROM APPS.FND_FLEX_VALUES_VL FFV
WHERE FFV.FLEX_VALUE_SET_ID IN
(SELECT FIV.FLEX_VALUE_SET_ID
FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
AND (ID_FLEX_CODE = 'GL#')
AND (APPLICATION_ID = 101)
AND FIV.SEGMENT_NUM = P_SEGMENT_NUM) --子值集段序号
AND FFV.FLEX_VALUE = P_SEGMENT --子值集段值
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
V_DESC := P_SEGMENT;
END;
END;
RETURN V_DESC;
EXCEPTION
/* WHEN TOO_MANY_ROWS THEN
RETURN 'TOO_MANY_ROWS';
WHEN NO_DATA_FOUND THEN
RETURN 'NO_DATA_FOUND';*/
WHEN OTHERS THEN
RETURN NULL;
END;
SELECT A.SEGMENT1
,MEW_GET_ACCOUNT_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
,NULL
,A.SEGMENT1
,1) SEGMENT1_DESC
,A.SEGMENT2
,MEW_GET_ACCOUNT_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
,NULL
,A.SEGMENT2
,2) SEGMENT2_DESC
,A.SEGMENT3
,MEW_GET_ACCOUNT_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
,NULL
,A.SEGMENT3
,3) SEGMENT3_DESC
,A.SEGMENT4
,MEW_GET_ACCOUNT_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
,NULL
,A.SEGMENT4
,4) SEGMENT4_DESC
,A.SEGMENT5
,MEW_GET_ACCOUNT_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
,A.SEGMENT4
,A.SEGMENT5
,5) SEGMENT5_DESC
,A.SEGMENT6
,MEW_GET_ACCOUNT_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
,A.SEGMENT5
,A.SEGMENT6
,6) SEGMENT6_DESC
,A.SEGMENT7
,MEW_GET_ACCOUNT_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
,A.SEGMENT6
,A.SEGMENT7
,7) SEGMENT7_DESC
,A.CONCATENATED_SEGMENTS
FROM GL_CODE_COMBINATIONS_KFV A
WHERE A.code_combination_id = 943564;