转：通过API获取和创建 Oracle GL Account Code Combination ID’s (CCID’s)的几种方法汇总

1] FND_FLEX_EXT.GET_COMBINATION_ID:

This API Finds combination_id for given set of key flexfield segment values. Segment values must be input in segments(1) – segments(n_segments) in the order displayed.

It also creates a new combination if it is valid and the flexfield allows dynamic inserts and the combination does not already exist. It commit the transaction soon after calling this function since if a combination is created it will prevent other users creating similar combinations on any flexfield until a commit is issued.

It performs all checks on values including security and cross-validation. Value security rules will be checked for the current user identified in the FND_GLOBAL package.

Generally pass in SYSDATE for validation date. If validation date is null, this function considers expired values valid and checks all cross-validation rules even if they are outdated.

This function returns TRUE if combination valid or FALSE and sets error message using FND_MESSAGE utility on error or if invalid. If this function returns FALSE, use GET_MESSAGE to get the text of the error message in the language of the database, or GET_ENCODED_MESSAGE to get the error message in a language-independent encoded format.

The Combination_id output may be NULL if combination is invalid.

 

SET serveroutput ON;
DECLARE  
    l_application_short_name VARCHAR2(240);
  l_key_flex_code          VARCHAR2(240);
  l_structure_num          NUMBER;  
  l_validation_date        DATE;  
  n_segments               NUMBER;  
  SEGMENTS                 APPS.FND_FLEX_EXT.SEGMENTARRAY;  
  l_combination_id         NUMBER;  
  l_data_set               NUMBER;  
  l_return                 BOOLEAN;  
  l_message                VARCHAR2(240);
BEGIN  
    l_application_short_name := 'SQLGL';
  l_key_flex_code          := 'GL#';   
  SELECT id_flex_num  
  INTO l_structure_num  
  FROM apps.fnd_id_flex_structures  
  WHERE ID_FLEX_CODE        = 'GL#'  
  AND ID_FLEX_STRUCTURE_CODE=<ACCOUNTING_FLEXFIELD>;   
  l_validation_date        := SYSDATE;  
  n_segments               := 6;  
  segments(1)              := '00101';  
  segments(2)              := '28506';  
  segments(3)              := '00000';  
  segments(4)              := '09063';  
  segments(5)              := '00000';  
  segments(6)              := '00000';  
  l_data_set               := NULL;   
  l_return := FND_FLEX_EXT.GET_COMBINATION_ID(                    
31                                           application_short_name => l_application_short_name,
32                                           key_flex_code          => l_key_flex_code,                    
33                                           structure_number       => l_structure_num,                    
34                                           validation_date        => l_validation_date,                    
35                                           n_segments             => n_segments,                    
36                                           segments               => segments,                    
37                                           combination_id         => l_combination_id,                    
38                                           data_set               => l_data_set);  
  l_message:= FND_FLEX_EXT.GET_MESSAGE;   
  IF l_return THEN    
      DBMS_OUTPUT.PUT_LINE('l_Return = TRUE');    
      DBMS_OUTPUT.PUT_LINE('COMBINATION_ID = ' || l_combination_id);  
  ELSE    DBMS_OUTPUT.PUT_LINE('Error: '||l_message);  
  END IF;

 

2] FND_FLEX_EXT.get_ccid:

This API gets combination id for the specified key flexfield segments.It is identical to get_combination_id() except this function takes segment values in a string concatenated by the segment  delimiter for this flexfield, and returns a positive combination id if valid or 0 on error.。

 

Declare
  v_ChartOfAccoutsId Number := 50268; --GL帐户键弹性域结构ID
  v_AccountName      Varchar2(2000) := '11.110822.4105160101.00.000000000000.000000.000000' --GL帐户
  v_CodeCombinationId Number; --GL帐户ID
Begin
  v_CodeCombinationId := Fnd_Flex_ext.get_ccid(application_short_name => 'SQLGL', --用应产品简称
 7                                                key_flex_code          => 'GL#', --键弹性域代码
 8                                                structure_number       => v_ChartOfAccoutsId, --键弹性域结构编号
 9                                                validation_date        => to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS'), --帐户有效日期
10                                                concatenated_segments  => v_AccountName); --
  If (Nvl(v_CodeCombinationId, 0) = 0) Then
    Dbms_Output.put_line('GL帐户组合失败');
  Else
    Dbms_Output.put_line('获得GL帐户ID:' || v_CodeCombinationId);
  End If;
End;

 

 

3] FND_FLEX_KEYVAL.VALIDATE_SEGS:

These key flexfields server validations API are a low level interface to key flexfields validation.  They are designed to allow access to all the flexfields functionality, and to allow the user to get only the information they need in return.  Because of their generality, these functions are more difficult to use than those in the FND_FLEX_EXT package.  Oracle strongly suggests using the functions in FND_FLEX_EXT package if at all possible.

This function finds combination from given segment values.  Segments are passed in as a concatenated string in increasing order of segment_number (display order).

Various Operations that can be performed are:

• ‘FIND_COMBINATION’ – Combination must already exist.所给定的组合字段必须在系统中已经被定义
• ‘CREATE_COMBINATION’ – Combination is created if doesn’t exist.如果组合字段不存在，则在系统中创建该组合
• ‘CREATE_COMB_NO_AT’ – same as create_combination but does not use an autonomous transaction.功能和CREATE_COMBINATION相同，只是不像前者要使用相对独立的子事务autonomous transaction
• ‘CHECK_COMBINATION’ – Checks if combination valid, doesn’t create.只是检查一下给定的组合字段是否合法，不管检查结果如何，都不创建新的组合
• ‘DEFAULT_COMBINATION’ – Returns minimal default combination.返回默认的最小组合
• ‘CHECK_SEGMENTS’ – Validates segments individually.分别验证字段

If validation date is NULL checks all cross-validation rules. It returns TRUE if combination valid or FALSE and sets error message on server if invalid. Use the default values if you do not want any special functionality.

 

SET serveroutput ON;
DECLARE
  l_segment1          GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
  l_segment2          GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
  l_segment3          GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
  l_segment4          GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
  l_segment5          GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
  l_segment6          GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
  l_valid_combination BOOLEAN;
  l_cr_combination    BOOLEAN;
  l_ccid              GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
  l_structure_num     FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
  l_conc_segs         GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
  p_error_msg1        VARCHAR2(240);
  p_error_msg2        VARCHAR2(240);
BEGIN
  l_segment1  := '00101';
  l_segment2  := '28506';
  l_segment3  := '00000';
  l_segment4  := '14302';
  l_segment5  := '00455';
  l_segment6  := '00000';
  l_conc_segs := l_segment1 || '.' || l_segment2 || '.' || l_segment3 || '.' ||
                 l_segment4 || '.' || l_segment5 || '.' || l_segment6;
  BEGIN
    SELECT id_flex_num
      INTO l_structure_num
      FROM apps.fnd_id_flex_structures
     WHERE id_flex_code = 'GL#'
       AND id_flex_structure_code = 'EPC_GL_ACCOUNTING_FLEXFIELD';
  EXCEPTION
    WHEN OTHERS THEN
      l_structure_num := NULL;
  END;
  ---------------Check if CCID exits with the above Concatenated Segments---------------  
  BEGIN
    SELECT code_combination_id
      INTO l_ccid
      FROM apps.gl_code_combinations_kfv
     WHERE concatenated_segments = l_conc_segs;
  EXCEPTION
    WHEN OTHERS THEN
      l_ccid := NULL;
  END;
  IF l_ccid IS NOT NULL THEN
    ------------------------The CCID is Available----------------------    
    DBMS_OUTPUT.PUT_LINE('COMBINATION_ID= ' || l_ccid);
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a New Combination. Validation Starts....');
    ------------Validate the New Combination--------------------------    
    l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS(operation        => 'CHECK_COMBINATION',
52                                                               appl_short_name  => 'SQLGL',
53                                                               key_flex_code    => 'GL#',
54                                                               structure_number => L_STRUCTURE_NUM,
55                                                               concat_segments  => L_CONC_SEGS);
    p_error_msg1        := FND_FLEX_KEYVAL.ERROR_MESSAGE;
    IF l_valid_combination then
      DBMS_OUTPUT.PUT_LINE('Validation Successful! Creating the Combination...');
      -------------------Create the New CCID--------------------------       
      L_CR_COMBINATION := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS(operation        => 'CREATE_COMBINATION',
                                                             appl_short_name  => 'SQLGL',
                                                             key_flex_code    => 'GL#',
                                                             structure_number => L_STRUCTURE_NUM,
                                                             concat_segments  => L_CONC_SEGS);
      p_error_msg2     := FND_FLEX_KEYVAL.ERROR_MESSAGE;
      IF l_cr_combination THEN
        -------------------Fetch the New CCID--------------------------        
        SELECT code_combination_id
          INTO l_ccid
          FROM apps.gl_code_combinations_kfv
         WHERE concatenated_segments = l_conc_segs;
        DBMS_OUTPUT.PUT_LINE('NEW COMBINATION_ID = ' || l_ccid);
      ELSE
        -------------Error in creating a combination-----------------        
        DBMS_OUTPUT.PUT_LINE('Error in creating the combination: ' ||
                             p_error_msg2);
      END IF;
    ELSE
      --------The segments in the account string are not defined in gl value set----------      
      DBMS_OUTPUT.PUT_LINE('Error in validating the combination: ' ||
                           p_error_msg1);
    END IF;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
END;

 

- appl_short_name 应用程序简称，例如总账模块对应的应用简称为SQLGL

- key_flex_code 键弹性域代码，可以在键弹性域定义界面找到，例如 GL#等等

- structure_number  指定键弹性域下的某个结构NUM，可以在键弹性域定义界面找到，对应字段为ID_FLEX_NUM

- concat_segments  要获取组合描述字段的对应的“值“组合字段，例如: 102-00-0000-50110000-000-000000

一旦初始化验证字段成功，那么描述组合字段便可以通过调用fnd_flex_keyval.concatenated_descriptions()来得到了。

SQL> declare
     pi_concat_segs varchar2(4000) := '102-00-0000-50110000-000-000000';
     po_desc varchar2(4000);
    BEGIN
      IF fnd_flex_keyval.validate_segs(operation        => 'FIND_COMBINATION',
                                       appl_short_name  => 'SQLGL',
                                       key_flex_code    => 'GL#',
                                       structure_number => 101,
                                       concat_segments  => pi_concat_segs ) THEN
       po_desc := fnd_flex_keyval.concatenated_descriptions();
     ELSE
       po_desc := null;
     END IF;
       dbms_output.put_line(po_desc);
   END;
   /
xxxxx -Corp-Common-F/G Sales \- External \- Ge-External-Spare 
PL/SQL procedure successfully completed