DECLARE

  l_ccid NUMBER;

  l_msg  VARCHAR2(1000);

  l_chart_of_account_id NUMBER;

  l_set_of_book_id NUMBER;

BEGIN

  l_set_of_book_id := fnd_profile.value('GL_SET_OF_BKS_ID');

  SELECT gsob.chart_of_accounts_id

    INTO l_chart_of_account_id

    FROM gl_sets_of_books gsob

  WHERE gsob.set_of_books_id = 1001;

  dbms_output.put_line('l_set_of_book_id: '||l_set_of_book_id||

  ', l_chart_of_account_id: '||l_chart_of_account_id);

  -- get the code combination id according to the concatenated segments

  -- 已存在的组合,返回CCID,不存在的组合,自动创建并返回CCID

  l_ccid := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',

                                  key_flex_code          => 'GL#',

                                  structure_number       => l_chart_of_account_id,

                                  validation_date        => to_char(SYSDATE,

                                                                    'DD-MON-YYYY'),

                                  concatenated_segments  => 'B21.50800.01000.000.000000.01002.000.0100');

  IF l_ccid = 0 THEN

    -- get the error message

    l_msg := fnd_message.get;

    -- output the error message

    dbms_output.put_line(l_msg);

  ELSE

    -- output the code combination id

    dbms_output.put_line(l_ccid);

  END IF;

END;